<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title>Python and Oracle Database Tutorial: The New Wave of Scripting</title>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />

<link rel="stylesheet" href="resources/base.css" type="text/css"/>
<link rel="shortcut icon" type="image/x-icon" href="resources/favicon.ico"/>
</head>
<body bgcolor="#ffffff" text="#000000">
  <div class="oracleHeader">
    <div class="container">
      <a class="oracleLogo" href="https://www.oracle.com/">Oracle</a>
    </div>
  </div>
  <header class="header" role="banner">
    <div class="container">
      <div class="headerLogoContainer">
        <img class="headerLogo" alt="python-oracledb logo" src="resources/logo.png" />
      </div>
      <div class="headerContent">
        <h1 class="headerTitle">Python and Oracle Database Tutorial: The New Wave of Scripting</h1>
        <nav class="headerNav" role="navigation">
        </nav>
      </div>
    </div>

  </header>

  <h2>Contents</h2>

  <ul>
    <li><a href="#overview" >Overview</a></li>
    <br>
    <li><a href="#setup" >Setup</a></li>
    <br>
    <li><a href="#connecting">1. Connecting to Oracle</a>
      <ul>
        <li>1.1 Creating a basic connection</li>
        <li>1.2 Indentation indicates code structure</li>
        <li>1.3 Executing a query</li>
        <li>1.4 Closing connections</li>
        <li>1.5 Checking versions</li>
        <li>1.6 Using the ConnectParams builder class</li>
        <li>1.7 Checking Connection Health</li>
      </ul>
    </li>
    <li><a href="#pooling">2. Connection Pooling</a>
      <ul>
        <li>2.1 Connection pooling</li>
        <li>2.2 Connection pool experiments</li>
        <li>2.3 Creating a DRCP Connection</li>
        <li>2.4 Connection pooling and DRCP</li>
        <li>2.5 More DRCP investigation</li>
      </ul>
    </li>
    <li><a href="#fetching">3. Fetching Data</a>
      <ul>
        <li>3.1 A simple query</li>
        <li>3.2 Using fetchone()</li>
        <li>3.3 Using fetchmany()</li>
        <li>3.4 Tuning with arraysize and prefetchrows</li>
      </ul>
    </li>
    <li><a href="#binding">4. Binding Data</a>
      <ul>
        <li>4.1 Binding in queries</li>
        <li>4.2 Binding in inserts</li>
        <li>4.3 Batcherrors</li>
      </ul>
    </li>
    <li><a href="#plsql">5. PL/SQL</a>
      <ul>
        <li>5.1 PL/SQL functions</li>
        <li>5.2 PL/SQL procedures</li>
      </ul>
    </li>
    <li><a href="#handlers">6. Type Handlers</a>
      <ul>
        <li>6.1 Basic output type handler</li>
        <li>6.2 Output type handlers and variable converters</li>
        <li>6.3 Input type handlers</li>
      </ul>
    </li>
    <li> <a href="#lobs">7. LOBs</a>
      <ul>
        <li>7.1 Fetching a CLOB using a locator</li>
        <li>7.2 Fetching a CLOB as a string</li>
      </ul>
    </li>
    <li> <a href="#json">8. JSON</a>
      <ul>
        <li>8.1 Inserting JSON</li>
        <li>8.2 Fetching JSON</li>
      </ul>
    </li>
    <li><a href="#vector">9. VECTORs</a>
      <ul>
        <li>9.1 Inserting a VECTOR</li>
        <li>9.2 Fetching a VECTOR</li>
        <li>9.3 Working with VECTORs and NumPy</li>
      </ul>
    </li>
    <li> <a href="#rowfactory">10. Rowfactory functions</a>
      <ul>
        <li>10.1 Rowfactory for mapping column names</li>
      </ul>
    </li>
    <li><a href="#subclass">11. Subclassing connections and cursors</a>
      <ul>
        <li>11.1 Subclassing connections</li>
        <li>11.2 Subclassing cursors</li>
      </ul>
    </li>
    <li><a href="#bindnamedobj">12. Binding named objects</a>
      <ul>
      <li>12.1 How to bind named objects</li>
      </ul>
    </li>
    <li><a href="#typehandlers">13. Input and Output Type Handlers with named objects</a>
      <ul>
      <li>13.1 Input type handlers with named objects</li>
      <li>13.2 Output type handlers with named objects</li>
      </ul>
    </li>
    <li><a href="#aq">14. Advanced Queuing</a>
      <ul>
        <li>14.1 Message passing with Oracle Advanced Queuing</li>
      </ul>
    </li>
    <li><a href="#scrollable">15. Scrollable cursors</a>
      <ul>
      <li>15.1 Working with scrollable cursors</li>
      </ul>
    </li>
    <li><a href="#dataframes">16. DataFrames</a>
      <ul>
        <li>16.1 Fetching DataFrames</li>
        <li>16.2 Inserting DataFrames</li>
      </ul>
    </li>
    <li><a href="#directpath">17. Direct Path Loads</a>
      <ul>
        <li>17.1 Using Direct Path Loads</li>
        <li>17.2 Inserting DataFrames with Direct Path Loads</li>
      </ul>
    </li>
    <li><a href="#asyncio">18. Concurrent Programming with asyncio</a>
      <ul>
        <li>18.1 Using asyncio</li>
      </ul>
    </li>
    <li><a href="#pipelining">19. Pipelining multiple operations</a>
      <ul>
        <li>19.1 Using Pipelining</li>
      </ul>
    </li>
    <li><a href="#thick">20. Python-oracledb Thick mode</a>
      <ul>
      <li>20.1 Review the Oracle Client library path</li>
      <li>20.2 Review the configuration files for thick mode</li>
      </ul>
    </li>
    <li><a href="#soda">21. Simple Oracle Document Access (SODA)</a>
      <ul>
        <li>21.1 Inserting JSON Documents</li>
        <li>21.2 Searching SODA Documents</li>
      </ul>
    </li>
    <li><a href="#summary" >Summary</a></li>
    <br>
    <li><a href="#primer" >Appendix: Python Primer</a></li>
    <br>
    <li><a href="#resources" >Resources</a></li>
</ul>

  <h2><a name="overview">Overview</a></h2>

  <p>This tutorial is a guide on using Python with Oracle Database. It contains
  both beginner and advanced materials. Choose the content that interests you
  and your skill level. The tutorial has scripts to run and modify, and has
  suggested solutions.</p>

  <p>Python is a popular general purpose dynamic scripting language. The
  python-oracledb driver provides access to Oracle Database from Python
  scripts. It is the successor to the obsolete cx_Oracle interface.  </p>

  <p>If you are new to Python, review the <a href="#primer">Appendix: Python
  Primer</a> to gain an understanding of the language. </p> <p>When you have
  finished this tutorial, we recommend reviewing the <a
  href="http://python-oracledb.readthedocs.org/en/latest/index.html"
  >python-oracledb documentation</a>.</p>

  <p>The original copy of these instructions that you are reading is <a
  href="https://oracle.github.io/python-oracledb/samples/tutorial/Python-and-Oracle-Database-The-New-Wave-of-Scripting.html"
  >here</a>.</p>

<h3><a name="architecture">Python-oracledb Architecture</a></h3>

<p>By default, python-oracledb runs in a &quot;Thin&quot; mode, which connects
directly to Oracle Database.  This mode does not need Oracle Client
libraries. However, some additional features are available when python-oracledb
uses them.  Python-oracledb applications that load the Oracle Client libraries
via an application script runtime option are said to be in &quot;Thick&quot;
mode. Both modes have comprehensive functionality supporting the Python
Database API v2.0 Specification, allowing python-oracledb to be use by popular
frameworks, ORMs, and other libraries.</p>

<p><img src="resources/python-oracledb-arch.svg" alt="Python python-oracledb
architecture" width="800"/></p> <p>The database can be on the same machine as
Python, or it can be remote.</p> <h2><a name="setup">Setup</a></h2>

  <ul>
    <li>
      <h4 id="accessdb">Get Access to Oracle Database</h4>

      <p>This tutorial assumes you have DBA access to Oracle Database. This is
      needed to grant some privileges and roles.</p>

      <p>Some examples require the latest version of Oracle Database, but most
      will work with older database versions.</p>

      <p>If you need a database, you can install Oracle Database Free on
      Linux or Windows from <a
      href="https://www.oracle.com/database/technologies/oracle-database-software-downloads.html#db_free"
      >oracle-database-software-downloads.html#db_free</a>.</p>

      <p>Alternatively use a container from <a
      href="https://container-registry.oracle.com/ords/ocr/ba/database/free"
      >container-registry.oracle.com</a>:</p>

      <pre>podman run -p 1521:1521 --name free -e ORACLE_PWD=mysecret
      container-registry.oracle.com/database/free:latest</pre>

      <p>Variants of the container can also be found at <a
      href="https://github.com/gvenzl/oci-oracle-free/blob/main/README.md"
      >github.com/gvenzl/oci-oracle-free/blob/main</a></p>

      <p>When the locally installed Free database or container is running, the
      privileged user name is "SYSTEM" and the connection string is
      "localhost/freepdb1". For examples that additionally need to connect to
      the root container database (not to be confused with a database running
      in a Docker container), the root connection string is
      "localhost/free".</p>

    </li>

    <li>
      <h4 id="installsw">Install Python and python-oracledb</h4>

      <p>Install Python 3 if not already available.  It can be obtained from
      your operating system package library or from <a
      href="https://www.python.org/">python.org</a>.  Use Python 3.9 or
      later.</p>

      <p>Install <a
      href="https://pypi.org/project/oracledb/">python-oracledb</a> with a
      command like:</p> <pre>python -m pip install oracledb --upgrade</pre>
      <p>or</p> <pre>python3 -m pip install oracledb --upgrade</pre>


    </li>

      <li>
        <h4 id="downloadscripts">Download the tutorial scripts</h4>

        <p>The Python scripts used in this example are in the <a
        href="https://github.com/oracle/python-oracledb/tree/main/samples/tutorial"
        >python-oracledb GitHub repository</a>.</p>

        <p>Download a zip file of the repository from <a
        href="https://github.com/oracle/python-oracledb/archive/main.zip"
        >here</a> and unzip it. Alternatively you can use 'git' to clone the
        repository:</p>

<pre>git clone --recursive https://github.com/oracle/python-oracledb.git</pre>

        <p>The <code>samples/tutorial</code> directory has scripts to run and
        modify. The <code>samples/tutorial/solutions</code> directory has
        scripts with suggested code changes. The
        <code>samples/tutorial/sql</code> directory has all the SQL scripts
        used by the Python files to create database tables and other
        objects.</p>

      </li>

      <li>
        <h4>Review the privileged database credentials used for creating the
        schema</h4>

        <p>Review <code>db_config_sys.py</code> in the <code>tutorial</code>
        directory. This file is included in other Python files for creating and
        dropping the tutorial user.</p>

        <p>Edit <code>db_config_sys.py</code> and change the default
        values to match the system connection information for your environment.
        Alternatively, you can set the given environment variables in your
        terminal window. For example, the default username is "<em>SYSTEM</em>"
        unless the environment variable "<em>PYTHON_SYSUSER</em>" contains a
        different username. The default system connection string is for the
        "<em>freepdb1</em>" database service on the same machine as Python. In
        Python Database API terminology, the connection string parameter is
        called the "data source name", or "dsn".  Using environment variables
        is convenient because you will not be asked to re-enter the password
        when you run scripts. The file <code>db_config_sys.py</code> looks
        like:</p>

<pre>
user = os.environ.get("PYTHON_SYSUSER", "SYSTEM")

dsn = os.environ.get("PYTHON_CONNECT_STRING", "localhost/freepdb1")

pw = os.environ.get("PYTHON_SYSPASSWORD")
if pw is None:
    pw = getpass.getpass("Enter password for %s: " % user)
</pre>

        <p>Substitute the admininstator values for your environment. If you are
        using Oracle Autonomous Database (ADB), use the <em>ADMIN</em> user
        instead of <em>SYSTEM</em>. The tutorial instructions may need
        adjusting, depending on how you have set up your environment.</p>

    </li>

      <li>
        <h4 id="createdbuser">Create a database user</h4>

        <p>If you have an existing user, you may be able to use it for most
        examples (though some examples may require extra permissions).</p>

        <p>If you need to create a new user for this tutorial, review the
        grants created in <code>samples/tutorial/sql/create_user.sql</code> by
        opening it in your favorite text editor. Then open a terminal window
        and run <code>create_user.py</code> to execute the
        <code>create_user.sql</code> script and create the sample user. This
        tutorial uses the name <code>pythondemo</code> by default:</p>

        <pre><strong>python create_user.py</strong></pre>

        <p>The example above connects as the <em>SYSTEM (or ADMIN</em> for
        ADB<em>) </em> user using <code>db_config_sys</code> file discussed in
        the earlier section.  The connection string is
        "<em>localhost/freepdb1</em>", meaning use the database service
        "<em>freepdb1</em>" running on localhost (the computer you are running
        your Python scripts on).</p>

        <p>If it runs successfully, you will see something similar below:</p>

<pre>Enter password for SYSTEM:
Enter password for pythondemo:
Creating user...
SQL File Name:  python-oracledb\samples\tutorial\sql\create_user.sql
Done.</pre>

        <p>The new user <em>pythondemo</em> is created.</p>

        <p>When the tutorial is finished, ensure that all the database sessions
        connected to the tutorial user <em>pythondemo</em> are closed and then
        run <code>python drop_user.py</code> to remove the tutorial user.</p>

      </li>

    <li>
      <h4 id="installsampleenv">Install the tables and other database objects for the tutorial</h4>

      <p>Once you have a database user, then you can create the key tutorial
      tables and database objects for the tutorial by running
      <code>setup_tutorial.py</code> (the environment setup file), using your
      values for the tutorial username, password and connection string:</p>

      <pre><strong>python setup_tutorial.py</strong></pre>

      <p>On successful completion of the run, You will see something like:</p>

      <pre>Setting up the sample tables and other DB objects for the tutorial...
SQL File Name:  python-oracledb/samples/tutorial/sql/setup_tutorial.sql
Done.</pre>

      <p>This will call the <code>setup_tutorial.sql</code> file from
      <code>tutorials/sql</code> directory to setup some sample tables and
      database objects required for running the examples in the tutorial.</p>

    </li>

     <li>
       <h4>Review the connection credentials used by the tutorial scripts</h4>

       <p>Review <code>db_config.py</code> (Thin mode), and
       <code>db_config.sql</code> files in the <code>tutorial</code> and
       <code>tutorial/sql </code>directories respectively. These are included
       in other Python and SQL files for setting up the database
       connection.</p>

       <p>Edit <code>db_config.py</code> and change the default values to
       match the connection information for your environment.  Alternatively,
       you can set the given environment variables in your terminal window. For
       example, the default username is "<em>pythondemo</em>" unless the
       environment variable "<em>PYTHON_USER</em>" contains a different
       username. The default connection string is for the '<em>freepdb1</em>'
       database service on the same machine as Python. In Python Database API
       terminology, the connection string parameter is called the "data source
       name", or "dsn".  Using environment variables is convenient because you
       will not be asked to re-enter the password when you run scripts:</p>

<pre>
user = os.environ.get("PYTHON_USER", "pythondemo")

dsn = os.environ.get("PYTHON_CONNECT_STRING", "localhost/freepdb1")

pw = os.environ.get("PYTHON_PASSWORD")
if pw is None:
    pw = getpass.getpass("Enter password for %s: " % user)
</pre>

       <p>Also, change the database username and connection string in the SQL
       configuration file <code>db_config.sql</code> based on your environment
       settings:</p>

<pre>
-- Default database username
def user = "<strong>pythondemo</strong>"

-- Default database connection string
def connect_string = "<strong>localhost/freepdb1</strong>"

-- Prompt for the password
accept pw char prompt 'Enter database password for &amp;user: ' hide
</pre>

       <p>The tutorial instructions may need adjusting, depending on how you
       have set up your environment.</p>

     </li>

</ul>

<h2><a name="connecting">1. Connecting to Oracle</a></h2>

<p>You can connect from Python to a local, remote or cloud Oracle
Database. <em>Documentation link for further reading: <a
href="https://python-oracledb.readthedocs.io/en/latest/user_guide/connection_handling.html"
>Connecting to Oracle Database</a></em>.</p>

<ul>
  <li>
    <h4>1.1 Creating a basic connection</h4>

    <p>Review the code contained in <code>connect.py</code>:</p>

    <pre>
import oracledb
import db_config

con = oracledb.connect(user=db_config.user, password=db_config.pw, dsn=db_config.dsn)
print("Database version:", con.version)
</pre>

    <p>The python-oracledb module is imported to provide the API for accessing
    the Oracle database. Many inbuilt and third-party modules can be included
    in Python scripts this way.</p>

    <p> The username, the password and the connection string that you
configured in the <code>db_config.py</code> module is passed to the
<code>connect()</code> method. By default, Oracle's Easy Connect connection
string syntax is used. It consists of the hostname of your machine,
<code>localhost</code>, and the database service name
<code>freepdb1</code>. (In Python Database API terminology, the connection
string parameter is called the "data source name", or "dsn").</p>

    <p>Open a command terminal and change to the <code>tutorial</code>
    directory:</p>

    <pre><strong>cd samples/tutorial</strong></pre>

    <p>Run the Python script:</p>

    <pre><strong>python connect.py</strong></pre>

    <p>The version number of the database should be displayed. An exception is
    raised if the connection fails. Adjust the username, password, or
    connection string parameters to invalid values to see the exception.</p>

    <p>Python-oracledb also supports other authentication methods such as
    "<em>token authentication</em>" and "<em>external authentication</em>",
    which allow connections without needing usernames and passwords to be
    embedded in the code. With external authentication, access could be
    enforced by, for example, an Oracle Wallet.</p>

  </li>

  <li>
    <h4>1.2 Indentation indicates code structure</h4>

    <p>Python uses whitespace to indicate code blocks. It does not use
    statement terminators, begin/end keywords, or braces.</p>

    <p><b>Note that the sample files use spaces, not tabs.</b></p>

    <p>Open <code>connect.py</code> in an editor. Indent the print statement
    with some spaces:</p>

    <pre>import oracledb
import db_config

con = oracledb.connect(user=db_config.user, password=db_config.pw, dsn=db_config.dsn)
  print("Database version:", con.version)
</pre>

    <p>Save the script and run it again:</p>

    <pre><strong>python connect.py</strong> </pre>

    <p>This raises an exception about the indentation. The number of spaces or
    tabs must be consistent in each block; otherwise, the Python interpreter
    will either raise an exception or execute code unexpectedly.  </p>

    <p>Python may not always be able to identify accidental from deliberate
    indentation. <em>Check if your indentation is correct before running each
    example.  Make sure to indent all statement blocks equally in the tutorial
    with spaces (not tabs).</em></p>

  </li>

  <li>
    <h4>1.3 Executing a query</h4>

    <p>Open <code>query.py</code> in an editor.  It looks like:</p>

    <pre>
import oracledb
import db_config

con = oracledb.connect(user=db_config.user, password=db_config.pw, dsn=db_config.dsn)
</pre>

    <p>Edit the file and add the code shown in bold below:</p>

<pre>
import oracledb
import db_config

con = oracledb.connect(user=db_config.user, password=db_config.pw, dsn=db_config.dsn)

<strong>cur = con.cursor()
cur.execute("select * from dept order by deptno")
res = cur.fetchall()
for row in res:
    print(row)</strong>
</pre>

    <p>Make sure the <code>print(row)</code> line is indented. This tutorial
    uses spaces, not tabs.</p>

    <p>The code executes a query and fetches all data.</p>

    <p>Save the file and run it:</p>

    <pre><strong>python query.py</strong></pre>

    <p>In each loop iteration, a new row is stored in <code>row</code> variable
    as a Python "tuple" and is displayed.</p>

    <p>Fetching data is described further in <a href="#fetching" >Section
    3</a>.</p>

  </li>

  <li>
    <h4>1.4 Closing connections</h4>

    <p>Connections and other resources used by python-oracledb will
    automatically be closed at the end of scope.  This is a common programming
    style that takes care of the correct order of resource closure.</p>

    <p>Resources can also be explicitly closed to free up database resources if
    they are no longer needed.  This is strongly recommended in blocks of code
    that remain active for some time.</p>

    <p>Open <code>query.py</code> in an editor and add calls to close the
    cursor and connection like:</p>

    <pre>
import oracledb
import db_config

con = oracledb.connect(user=db_config.user, password=db_config.pw, dsn=db_config.dsn)

cur = con.cursor()
cur.execute("select * from dept order by deptno")
res = cur.fetchall()
for row in res:
    print(row)

<strong>cur.close()</strong>
<strong>con.close()</strong>
</pre>

    <p>Running the script completes without error:</p>

    <pre><strong>python query.py</strong></pre>

    <p>If you swap the order of the two <code>close()</code> calls you will see
    an error.</p>

    <p>Often Python context managers are used to automatically scope resources
    and automatically close them. You might encounter Python code that uses
    them as <code>with</code> blocks like:</p>

    <pre>with oracledb.connect(user=db_config.user, password=db_config.pw, dsn=db_config.dsn) as con:
    with con.cursor() as cur:
        cur.execute("select * from dept order by deptno")
        res = cur.fetchall()
        for row in res:
            print(row)</pre>

    <p>For ease of editing and getting correct indentation this tutorial
    doesn't use context managers, but they are recommended.</p>

  </li>

  <li>
    <h4>1.5 Checking versions</h4>

    <p>Review the code contained in <code>versions.py</code>:</p>

    <pre>
import oracledb
import db_config

con = oracledb.connect(user=db_config.user, password=db_config.pw, dsn=db_config.dsn)

print(oracledb.__version__) # two underscores before and after the version</pre>

    <p>Run the script in a terminal window:</p>

    <pre><strong>python versions.py</strong></pre>

    <p>This gives the version of the python-oracledb interface.</p>

    <p>Edit the file to print the version of the database, and the Oracle
    client libraries used by python-oracledb:</p>

    <pre>
import oracledb
import db_config

con = oracledb.connect(user=db_config.user, password=db_config.pw, dsn=db_config.dsn)

print(oracledb.__version__)
<strong>print("Database version:", con.version)</strong>
</pre>

    <p>When the script is run, it will display something like:</p>

    <pre>
3.1.0
Database version: 23.7.0.0.0</pre>

    <p>Any python-oracledb installation can connect to older and newer Oracle
    Database versions.  By checking the Oracle Database version numbers, the
    application can make use of the best Oracle features available.</p>

  </li>

  <li>
    <h4>1.6 Using the ConnectParams builder class</h4>

    <p>To help encapsulate connection arguments, a connection property builder
    function <code>oracledb.ConnectParams()</code> can be used. It returns a
    <em>ConnectParams</em> object. The object can be passed to
    <code>oracledb.connect()</code> or <code>oracledb.create_pool()</code>.</p>

    <p>Open <code>connect_params2.py</code> in a text editor. It looks
    like:</p>

    <pre>import oracledb
import db_config

params = oracledb.ConnectParams(host="localhost", port=1521, service_name="freepdb1")
con = oracledb.connect(user=db_config.user, password=db_config.pw, params=params)
print("Database version:", con.version)</pre>

    <p>Edit the connection details to suit your environment.</p>

    <p>When the script is run (<code><strong>python
    connect_params2.py</strong></code>), it will display something like:</p>

    <pre>Database version: 23.7.0.0.</pre>

    <p>The list of parameters for the <code>ConnectParams</code> class is
    available in the python-oracledb documentation. The use of
    <code>ConnectParams()</code> is optional. You can continue pass individual
    parameters to connection and pool creation calls, if you like.</p>

    <p>Notes:</p>

    <ul>
      <li>If the <code>params</code> parameter is specified and keyword parameters are also specified, then the <code>params</code> parameter is updated with the values from the keyword parameters before being used to create the connection. </li>
      <li>If the <code>dsn</code> parameter is specified and the <code>params</code> parameter is specified, then the <code>params</code> parameter is updated with the contents of the <code>dsn</code> parameter before being used to create the connection.</li>
    </ul>
  </li>

  <li>
    <h4>1.7 Checking Connection Health</h4>

    <p>The function <code>Connection.is_healthy()</code> checks the usability
    of a database connection locally. This function returns a boolean value
    indicating the health status of a connection.</p>

    <p>Connections may become unusable in several cases, such as if the network
    socket is broken, if an Oracle error indicates the connection is unusable
    or after receiving a planned down notification from the database.  This
    function is best used before starting a new database request on an existing
    standalone connection. Pooled connections internally perform this check
    before returning a connection to the application. If this function returns
    <code>False</code>, the connection should be not be used by the application
    and a new connection should be established instead.</p>

    <p>Open <code>connect_health.py</code> in a text editor. It looks like:</p>

    <pre>import oracledb
import db_config

con = oracledb.connect(user=db_config.user, password=db_config.pw, dsn=db_config.dsn)
if con.is_healthy():
    print("Healthy connection!")
else:
    print("Unusable connection. Please check the database and network settings.")</pre>

    <p>Run the script in a terminal window:</p>

    <pre>python connect_health.py</pre>

    <p>It will display (when the connection is OK):</p>

    <pre>Healthy Connection!</pre>

    <p>To fully check a connection's health, use <code>Connection.ping()</code>
    which performs a round-trip to the database and throws an exception if the
    connection is not usable. Pinging the database impacts ultimate
    scalability, so think carefully before adding it to your applications.
    Also, explicit pinging is generally not needed when you use connection
    pooling since the pool internally handles dead connection detection.</p>

  </li>
</ul>

<h2><a name="pooling">2. Connection Pooling</a></h2>

<p>Connection pooling is important for performance when multi-threaded
applications frequently connect and disconnect from the database. Pooling also
gives the best support for Oracle's High Availability (HA) features.
<em>Documentation link for further reading: <a
href="https://python-oracledb.readthedocs.io/en/latest/user_guide/connection_handling.html#connection-pooling">Connection
Pooling</a></em>.</p>

<ul>
  <li> <h4>2.1 Connection pooling</h4>

    <p>Review the code contained in <code>connect_pool.py</code>:</p>

    <pre>
import oracledb
import threading
import db_config

pool = oracledb.<strong>create_pool</strong>(user=db_config.user, password=db_config.pw, dsn=db_config.dsn,
                            min=2, max=5, increment=1, getmode=oracledb.POOL_GETMODE_WAIT)

def Query():
    con = pool.<strong>acquire</strong>()
    cur = con.cursor()
    for i in range(4):
        cur.execute("select myseq.nextval from dual")
        seqval, = cur.fetchone()
        print("Thread", threading.current_thread().name, "fetched sequence =", seqval)

thread1 = threading.Thread(name='#1', target=Query)
thread1.start()

thread2 = threading.Thread(name='#2', target=Query)
thread2.start()

thread1.join()
thread2.join()

print("All done!")
</pre>

    <p>The <code>create_pool()</code> function creates a pool of Oracle
    connections for the user.  Connections in the pool can be used by
    python-oracledb by calling <code>pool.acquire()</code>.  The initial pool
    size is 2 connections.  The maximum size is 5 connections. When the pool
    needs to grow, then a single new connection will be created at a time based
    on the <code>increment</code> parameter. The pool can shrink back to the
    minimum size of 2 when the connections are no longer in use.</p>

    <p>The <code>def Query():</code> line creates a method that is called by
    each thread.</p>

    <p>In the <code>Query()</code> method, the <code>pool.acquire()</code> call
    gets one connection from the pool (as long as less than 5 are already in
    use).  This connection is used in a loop of 4 iterations to query the
    sequence <code>myseq</code>. At the end of the method, python-oracledb will
    automatically close the cursor and release the connection back to the pool
    for reuse.</p>

    <p>The <code>seqval, = cur.fetchone()</code> line fetches a row and puts
    the single value contained in the result tuple into the variable
    <code>seqval</code>. Without the comma, the value in <code>seqval</code>
    would be a tuple like "<code>(1,)</code>".</p>

    <p>Two threads are created, each invoking the <code>Query()</code>
    method.</p>

    <p>In a command terminal, run:</p>

    <pre><strong>python connect_pool.py</strong></pre>

    <p>The output shows the interleaved query results as each thread fetches
    values independently.  The order of interleaving may vary from run to
    run.</p>

  </li>

  <li>
    <h4>2.2 Connection pool experiments</h4>

    <p>Review <code>connect_pool2.py</code>, which has a loop for the number of
    threads, each iteration invoking the <code>Query()</code> method:</p>

    <pre>
import oracledb
import threading
import db_config

pool = oracledb.create_pool(user=db_config.user, password=db_config.pw, dsn=db_config.dsn,
                            min=2, max=5, increment=1, getmode=oracledb.POOL_GETMODE_WAIT)

def Query():
    con = pool.acquire()
    cur = con.cursor()
    for i in range(4):
        cur.execute("select myseq.nextval from dual")
        seqval, = cur.fetchone()
        print("Thread", threading.current_thread().name, "fetched sequence =", seqval)

<strong>number_of_threads = 2
thread_array = []

for i in range(number_of_threads):
    thread = threading.Thread(name='#' + str(i), target=Query)
    thread_array.append(thread)
    thread.start()

for t in thread_array:
    t.join()</strong>

print("All done!")
</pre>

    <p>In a command terminal, run:</p>

    <pre><strong>python connect_pool2.py</strong></pre>

    <p>Experiment with different values of the pool parameters and
    <code>numberOfThreads</code>. </p>

    <p>Try changing <code>getmode</code> to
    <code>oracledb.POOL_GETMODE_NOWAIT</code>, reducing the maximum pool size to
    2, and increasing the number of threads to 3.  When
    <code>number_of_threads</code> exceeds the maximum size of the pool, the
    <code>acquire()</code> call will now generate an error such as
    "<em>DPY-4005: timed out waiting for the connection pool to return a
    connection</em>".  </p>

    <p>Pool configurations where <code>min</code> is the same as
    <code>max</code> (and <code>increment = 0</code>) are often recommended as
    a best practice for the optimum performance. Pools with such configurations
    are referred to as &quot;<em>static pools</em>&quot;. This configuration
    avoids connection storms on the database server.</p>

  </li>

  <li>
    <h4>2.3 Creating a DRCP Connection</h4>

    <p>Database Resident Connection Pooling allows multiple Python processes on
    multiple machines to share a small pool of database server processes.</p>

    <p>Below left is a diagram without DRCP. Every application standalone
    connection (or python-oracledb connection-pool connection) has its own
    database server process. Standalone application <code>connect()</code> and
    close calls require the expensive create and destroy of those database
    server processes.  Python-oracledb connection pools reduce these costs by
    keeping database server processes open, but every python-oracledb
    connection pool will require its own set of database server processes, even
    if they are not doing database work: these idle server processes consume
    database host resources. Below right is a diagram with DRCP.  Scripts and
    Python processes can share database servers from a pre-created pool of
    servers and return them when they are not in use.  </p>

    <table cellspacing="0" cellpadding="30" border="0" >
      <tr>
        <td>
          <img width="400" src="resources/python_nopool.png" alt="Picture of 3-tier application architecture without DRCP showing connections from multiple application processes each going to a server process in the database tier" />
          <div align="center"><p><strong>Without DRCP</strong></p></div>
        </td>
        <td>
          <img width="400" src="resources/python_pool.png" alt="Picture of 3-tier application architecture with DRCP showing connections from multiple application processes going to a pool of server processes in the database tier" />
          <div align="center"><p><strong>With DRCP</strong></p></div>
        </td>
      </tr>
    </table>

  <p>DRCP is useful when the database host machine does not have enough memory
  to handle the number of database server processes required. If DRCP is
  enabled, it is best used in conjunction with python-oracledb's connection
  pooling.  However, the default 'dedicated' server process model is generally
  recommended if the database host memory is large enough. This can be with or
  without a python-oracledb connection pool, depending on the connection
  rate.</p>

  <p>Batch scripts doing long running jobs should generally use dedicated
  connections. Both dedicated and DRCP servers can be used together in the same
  application or database.</p>

  <h4 id="startdrcp">Start the Database Resident Connection Pool (DRCP)</h4>

  <p name="startdrcp">If you are running a local or remote Oracle Database
  (that is not Oracle Autonomous Database), start the DRCP pool. Note that the
  DRCP pool is started in an Oracle Autonomous Database by default.</p>

  <p>Run SQL*Plus with SYSDBA privileges, for example:</p>

<pre>
sqlplus -l sys/syspassword@localhost/free as sysdba
</pre>

  <p>and execute the command:</p>

<pre>
execute dbms_connection_pool.start_pool()
</pre>

  <p>Note: If you are using Oracle Database 21c, run <code>show parameter
  enable_per_pdb_drcp</code> in SQL*Plus. If this shows TRUE, then you will
  need to run the <code>execute</code> command in a pluggable database, not a
  container database.</p>

  <h4>Connect to the Oracle Database through DRCP</h4>

  <p>Review the code contained in <code>connect_drcp.py</code>:</p>

  <pre>
import oracledb
import db_config

con = oracledb.connect(user=db_config.user, password=db_config.pw, dsn=db_config.dsn + "<strong>:pooled</strong>",
                       cclass=&quot;PYTHONDEMO&quot;, purity=oracledb.PURITY_SELF)
print("Database version:", con.version)
</pre>

  <p>This is similar to <code>connect.py</code> but
  &quot;<code>:pooled</code>&quot; is appended to the connection string,
  telling the database to use a pooled server. A Connection Class "PYTHONDEMO"
  is also passed into the <code>connect()</code> method to allow grouping of
  database servers to applications. Note that with Autonomous Database, the
  connection string has a different form, see the <a
  href="https://docs.oracle.com/en/cloud/paas/autonomous-database/serverless/adbsb/connect-drcp.html#GUID-E1337EC6-4A78-4199-84F0-A2739055F3FA
  " >ADB documentation</a>.</p>

  <p>The &quot;purity&quot; of the connection is defined as the
  <code>PURITY_SELF</code> constant, meaning the session state (such as the
  default date format) might be retained between connection calls, giving
  performance benefits. Session information will be discarded if a pooled
  server is later reused by an application with a different connection class
  name.</p>

  <p>Applications that should never share session information should use a
  different connection class and/or use <code>PURITY_NEW</code> to force
  creation of a new session. This reduces overall scalability but prevents
  applications from misusing the session information.  The default purity for
  connections created with <code>connect()</code> is
  <code>PURITY_NEW</code>.</p>

  <p>Run <code>connect_drcp.py </code>in a terminal window.</p>

  <pre><strong>python connect_drcp.py</strong></pre>

  <p>The output is simply the version of the database.</p>

  </li>

  <li>
    <h4>2.4 Connection pooling and DRCP</h4>

    <p>DRCP works well with python-oracledb's connection pooling.  The default
      purity for pooled connections is <code>PURITY_SELF</code>.</p>

    <p>Edit <code>connect_pool2.py</code>, reset any changed pool options, and
    modify it to use DRCP:</p>

    <pre>
import oracledb
import threading
import db_config

pool = oracledb.create_pool(user=db_config.user, password=db_config.pw, dsn=db_config.dsn <strong>+ ":pooled"</strong>,
                            min=2, max=5, increment=1, getmode=oracledb.POOL_GETMODE_WAIT,
                            <strong>cclass="PYTHONDEMO", purity=oracledb.PURITY_SELF</strong>)

def Query():
    con = pool.acquire()
    cur = conn.cursor()
    for i in range(4):
        cur.execute("select myseq.nextval from dual")
        seqval, = cur.fetchone()
        print("Thread", threading.current_thread().name, "fetched sequence =", seqval)

numberOfThreads = 2
threadArray = []

for i in range(numberOfThreads):
    thread = threading.Thread(name='#' + str(i), target=Query)
    threadArray.append(thread)
    thread.start()

for t in threadArray:
    t.join()

print("All done!")
</pre>

    <p>The script logic does not need to be changed to benefit from DRCP
    connection pooling.</p>

    <p>Run the script in a terminal window:</p>

    <pre><strong>python connect_pool2.py</strong></pre>

    <p>Optionally, you can run <strong>drcp_query.py</strong> to check the DRCP
    pool statistics.</p>

    <pre><strong>python drcp_query.py</strong></pre>

    <p>This will prompt for the SYSTEM (or ADMIN user), the password, and the
    database connection string. For running the file, you will need to connect
    to the root container database in Oracle Database 19c or lower.  From
    Oracle Database 21c onwards, you can optionally enable DRCP in pluggable
    databases.</p>

    <p>Note that with ADB, this view does not contain rows, so running this
    script is not useful. For other Oracle Databases, the script shows the
    number of connection requests made to the pool since the database was
    started ("NUM_REQUESTS"), how many of those reused a pooled server's
    session ("NUM_HITS"), and how many had to create new sessions
    ("NUM_MISSES"). Typically the goal is a low number of misses.</p>

    <p>If the file is run successfully, you should see something like:</p>

    <pre>Looking at DRCP Pool stats...

(CCLASS_NAME, NUM_REQUESTS, NUM_HITS, NUM_MISSES)
-------------------------------------------------
('PYTHONDEMO.SHARED', 5, 0, 5)
('PYTHONDEMO.PYTHONDEMO', 4, 2, 2)
('SYSTEM.SHARED', 11, 0, 11)
Done.</pre>

    <p>To see the pool configuration, you can query DBA_CPOOL_INFO.</p>

  </li>

  <li>
    <h4>2.5 More DRCP investigation</h4>

    <p>To further explore the behaviors of python-oracledb connection pooling
    and DRCP pooling, you could try changing the purity to
    <code>oracledb.PURITY_NEW</code> to see the effect on the DRCP NUM_MISSES
    statistic.</p>

    <p>Another experiment is to include the <code>time</code> module at the
    file top:</p>

    <pre>
import time</pre>

    <p>and add calls to <code>time.sleep(1)</code> in the code, for example in
    the query loop.  Then look at the way the threads execute.  Use
    <code>drcp_query.sql</code> to monitor the pool's behavior.</p>

    </li>
  </ul>

  <h2><a name="fetching">3. Fetching Data</a> </h2>

  <p>Executing SELECT queries is the primary way to get data from Oracle
  Database. <em>Documentation link for further reading: <a
  href="https://python-oracledb.readthedocs.io/en/latest/user_guide/sql_execution.html"
  >SQL Queries</a></em>.</p>

  <ul>
    <li><h4>3.1 A simple query</h4>

      <p>There are several functions you can use to query an Oracle database,
      but the basics of querying are always the same:</p>

      <p>1. Execute the statement.<br />
      2. Bind data values (optional).<br />
      3. Fetch the results from the database.</p>

      <p>Review the code contained in <code>query2.py</code>:</p>

      <pre>
import oracledb
import db_config

con = oracledb.connect(user=db_config.user, password=db_config.pw, dsn=db_config.dsn)

cur = con.cursor()
cur.execute("select * from dept order by deptno")
for deptno, dname, loc in cur:
    print("Department number: ", deptno)
    print("Department name: ", dname)
    print("Department location:", loc)
</pre>

      <p>The <code>cursor()</code> method opens a cursor for statements to
      use.</p>

      <p>The <code>execute()</code> method parses and executes the
      statement.</p>

      <p>The loop fetches each row from the cursor and unpacks the returned
      tuple into the variables <code>deptno</code>, <code>dname</code>,
      <code>loc</code>, which are then printed.</p>

      <p>Run the script in a terminal window:</p>

      <pre><strong>python query2.py</strong></pre>

      <p>The output is:</p>

      <pre>Department number:  10
Department name:  ACCOUNTING
Department location: NEW YORK
Department number:  20
Department name:  RESEARCH
Department location: DALLAS
Department number:  30
Department name:  SALES
Department location: CHICAGO
Department number:  40
Department name:  OPERATIONS
Department location: BOSTON</pre>

    </li>

    <li>
      <h4>3.2 Using fetchone()</h4>

      <p>When the number of rows is large, the <code>fetchall()</code> call may
      use too much memory.</p>

      <p>Review the code contained in <code>query_one.py</code>:</p>

      <pre>
import oracledb
import db_config

con = oracledb.connect(user=db_config.user, password=db_config.pw, password=db_config.dsn)
cur = con.cursor()

cur.execute("select * from dept order by deptno")
row = cur.fetchone()
print(row)

row = cur.fetchone()
print(row)
</pre>

      <p>This uses the <code>fetchone()</code> method to return just a single
      row as a tuple. When called multiple time, consecutive rows are
      returned:</p>

      <p>Run the script in a terminal window:</p>

      <pre><strong>python query_one.py</strong></pre>

      <p>The first two rows of the table are printed.</p>

    </li>

    <li>
      <h4>3.3 Using fetchmany()</h4>

      <p>Review the code contained in <code>query_many.py</code>:</p>

      <pre>
import oracledb
import db_config

con = oracledb.connect(user=db_config.user, password=db_config.pw, dsn=db_config.dsn)
cur = con.cursor()

cur.execute("select * from dept order by deptno")
num_rows =  3
res = cur.fetchmany(num_rows)
print(res)
</pre>

      <p>The <code>fetchmany()</code> method returns a list of tuples. By
      default the maximum number of rows that can be returned is specified by
      the cursor attribute <code>arraysize</code> (which defaults to 100).
      Here the <code>num_rows</code> parameter specifies that three rows should
      be returned.</p>

      <p>Run the script in a terminal window:</p>

      <pre><strong>python query_many.py</strong></pre>

      <p>The first three rows of the table are returned as a list (Python's
      name for an array) of tuples.</p>

      <p>You can access elements of the lists by position indexes.  To see
      this, edit the file and add:</p>

      <pre>
<strong>print(res[0])</strong>    # first row
<strong>print(res[0][1])</strong> # second element of first row
</pre>

    </li>

    <li>
      <h4>3.4 Tuning with arraysize and prefetchrows</h4>

      <p>This section demonstrates a way to improve query performance by
      increasing the number of rows returned in each batch from Oracle Database
      to the Python program.</p>

      <p>Row prefetching and array fetching are internal buffering techniques
      to reduce round-trips to the database. The difference is the code layer
      that is doing the buffering, and when the buffering occurs.</p>

      <p>The <a href="#installsampleenv">environment setup file</a> has already
      created the <em>bigtab</em> table with a large number of rows (to be used
      by the <code>query_arraysize.py</code> file) by internally running the
      sql script below:</p>

      <pre>create table bigtab (mycol varchar2(20));

begin
 for i in 1..20000
 loop
  insert into bigtab (mycol) values (dbms_random.string('A',20));
 end loop;
end;</pre>

      <p>The setup file has also inserted around 20000 string values in the
      <em>bigtab</em> table.</p>

      <p>Review the code contained in <code>query_arraysize.py</code>:</p>

      <pre>
import oracledb
import time
import db_config

con = oracledb.connect(name=db_config.user, password=db_config.pw, dsn=db_config.dsn)

start = time.time()

cur = con.cursor()
cur.prefetchrows = 100
cur.arraysize = 100
cur.execute("select * from bigtab")
res = cur.fetchall()
# print(res)  # uncomment to display the query results

elapsed = (time.time() - start)
print(elapsed, "seconds")
</pre>

      <p>This uses the 'time' module to measure elapsed time of the query. The
      <em>prefetchrows</em> and <em>arraysize</em> values are 100. This causes
      batches of 100 records at a time to be returned from the database to a
      cache in Python.  These values can be tuned to reduce the number of
      &quot;round-trips&quot; made to the database, often reducing network load
      and reducing the number of context switches on the database server. The
      <code>fetchone()</code>, <code>fetchmany()</code> and
      <code>fetchall()</code> methods will read from the cache before
      requesting more data from the database.</p>

      <p>In a terminal window, run:</p>

      <pre><strong>python query_arraysize.py</strong></pre>

      <p>Rerun a few times to see the average times.</p>

      <p>Experiment with different prefetchrows and arraysize values.  For
      example, edit <code>query_arraysize.py</code> and change the arraysize
      to:</p>

      <pre>cur.arraysize = <strong>2000</strong></pre>

      <p>Rerun the script to compare the performance of different arraysize
      settings.</p>

      <p>In general, larger array sizes improve performance.  Depending on how
      fast your system is, you may need to use different values than those
      given here to see a meaningful time difference.</p>

      <p>There is a time/space tradeoff for increasing the values. Larger
      values will require more memory in Python for buffering the records. For
      queries that return a very large number of rows, you may prefer to leave
      the default prefetchrows value and only tune arraysize.</p>

      <p>If you know the query returns a fixed number of rows, for example, 20
      rows, then set arraysize to 20 and prefetchrows to 21.  The addition of
      one extra row for prefetchrows prevents a round-trip to check for
      end-of-fetch. The statement execution and fetch will take a total of one
      round-trip.  This minimizes the load on the database.</p>

      <p>If you know a query only returns a few records, decrease the arraysize
      from the default to reduce memory usage.</p>

  </li>
</ul>

<h2><a name="binding">4. Binding Data</a></h2>

<p>Bind variables enable you to re-execute statements with new data values
without the overhead of re-parsing the statement.  Binding improves code
reusability, improves application scalability, and can reduce the risk of SQL
injection attacks. Using bind variables is strongly recommended.
<em>Documentation link for further reading: <a
href="https://python-oracledb.readthedocs.io/en/latest/user_guide/bind.html"
>Using Bind Variables</a></em>.</p>

<ul>

  <li>
    <h4>4.1 Binding in queries</h4>

    <p>Review the code contained in <code>bind_query.py</code>:</p>

    <pre>
import oracledb
import db_config

con = oracledb.connect(user=db_config.user, password=db_config.pw, dsn=db_config.dsn)
cur = con.cursor()

sql = "select * from dept where deptno = :id order by deptno"

cur.execute(sql, id=20)
res = cur.fetchall()
print(res)

cur.execute(sql, id=10)
res = cur.fetchall()
print(res)
</pre>

    <p>The statement contains a bind variable "<code>:id</code>" placeholder.
    The statement is executed twice with different values for the
    <code>WHERE</code> clause.</p>

    <p>From a terminal window, run:</p>

    <pre><strong>python bind_query.py</strong></pre>

    <p>The output shows the details for the two departments.</p>

    <p>An arbitrary number of named arguments can be used in an
    <code>execute()</code> call.  Each argument name must match a bind variable
    name.  Alternatively, instead of passing multiple arguments you could pass
    a second argument to <code>execute()</code> that is a sequence or a
    dictionary.  Later examples show these syntaxes.</p>

    <p>To bind a database NULL, use the Python value <code>None</code>.</p>

    <p>python-oracledb uses a cache of executed statements.  As long as the
    statement you pass to <code>execute()</code> is in that cache, you can use
    different bind values and still avoid a full statement parse.  The
    statement cache size is configurable for each connection. To see the
    default statement cache size, edit <code>bind_query.py</code> and add a
    line at the end:</p>

<pre>
print(con.stmtcachesize)
</pre>

      <p>Re-run the script.</p>

      <p> You would set the statement cache size to the
      number of unique statements commonly executed in your applications.</p>

    </li>

    <li><h4>4.2 Binding in inserts</h4>

      <p>The <a href="#installsampleenv">environment setup file</a> has already
      created the <em>mytab</em> table (to be used by the
      <code>bind_insert.py</code> file) by internally running the sql script
      below:</p>

      <pre>create table mytab (id number, data varchar2(20), constraint my_pk primary key (id))</pre>

      <p>Now, review the code contained in <code>bind_insert.py</code>:</p>

      <pre>
import oracledb
import db_config

con = oracledb.connect(user=db_config.user, password=db_config.pw, dsn=db_config.dsn)
cur = con.cursor()

rows = [ (1, &quot;First&quot; ), (2, &quot;Second&quot; ),
         (3, &quot;Third&quot; ), (4, &quot;Fourth&quot; ),
         (5, &quot;Fifth&quot; ), (6, &quot;Sixth&quot; ),
         (7, &quot;Seventh&quot; ) ]

cur.executemany(&quot;insert into mytab(id, data) values (:1, :2)", rows)

# Now query the results back

cur2 = con.cursor()
cur2.execute('select * from mytab')
res = cur2.fetchall()
print(res)</pre>

      <p>The '<code>rows</code>' array contains the data to be inserted into
      the <em>mytab</em> table created earlier.</p>

      <p>The <code>executemany()</code> call inserts all rows.  This call uses
      "array binding", which is an efficient way to insert multiple
      records.</p>

      <p>The final part of the script queries the results back and displays
      them as a list of tuples.</p>

      <p>From a terminal window, run:</p>

      <pre><strong>python bind_insert.py</strong></pre>

      <p>The new results are automatically rolled back at the end of the
      script. Re-running the script will always show the same number of rows in
      the table.  Committing data is discussed later.</p>

    </li>

    <li>
      <h4>4.3 Batcherrors</h4>

      <p>The <code>Batcherrors</code> features allows invalid data to be
      identified while allowing valid data to be inserted.</p>

      <p>Edit the data values in <code>bind_insert.py</code> and
      create a row with a duplicate key:</p>

<pre>
rows = [ (1, &quot;First&quot; ), (2, &quot;Second&quot; ),
         (3, &quot;Third&quot; ), (4, &quot;Fourth&quot; ),
         (5, &quot;Fifth&quot; ), (6, &quot;Sixth&quot; ),
         <strong>(6, &quot;Duplicate&quot; ),</strong>
         (7, &quot;Seventh&quot; ) ]
</pre>

      <p>From a terminal window, run:</p>

      <pre><strong>python bind_insert.py</strong></pre>

      <p>The duplicate generates the error "ORA-00001: unique
      constraint (PYTHONHOL.MY_PK) violated".  The data is rolled back
      and the query returns no rows.</p>

      <p>Edit the file again and enable <code>batcherrors</code> like:</p>

      <pre>
import oracledb
import db_config

con = oracledb.connect(user=db_config.user, password=db_config.pw, dsn=db_config.dsn)
cur = con.cursor()

rows = [ (1, &quot;First&quot; ), (2, &quot;Second&quot; ),
         (3, &quot;Third&quot; ), (4, &quot;Fourth&quot; ),
         (5, &quot;Fifth&quot; ), (6, &quot;Sixth&quot; ),
         <strong>(6, &quot;Duplicate&quot; ),</strong>
         (7, &quot;Seventh&quot; ) ]

cur.executemany("insert into mytab(id, data) values (:1, :2)", rows<strong>, batcherrors=True</strong>)

<strong>for error in cur.getbatcherrors():
    print("Error", error.message.rstrip(), "at row offset", error.offset)</strong>

# Now query the results back

cur2 = con.cursor()
cur2.execute('select * from mytab')
res = cur2.fetchall()
print(res)
</pre>

      <p>Run the script in a terminal window:</p>

      <pre><strong>python bind_insert.py</strong></pre>

      <p>The new code shows the offending duplicate row: "ORA-00001: unique
      constraint (PYTHONDEMO.MY_PK) violated at row offset 6".  This indicates
      the 6th data value (counting from 0) had a problem.</p>

      <p>The other data gets inserted and is queried back.</p>

      <p>At the end of the script, python-oracledb will roll back an
      uncommitted transaction. If you want to commit results, you can use:</p>

      <pre>con.commit()</pre>

      <p>To force python-oracledb to roll back the transaction, use:</p>

      <pre>con.rollback()</pre>

   </li>

</ul>

<h2><a name="plsql">5. PL/SQL</a></h2>

<p>PL/SQL is Oracle's procedural language extension to SQL. PL/SQL procedures
  and functions are stored and run in the database. Using PL/SQL lets all
  database applications reuse logic, no matter how the application accesses the
  database. Many data-related operations can be performed in PL/SQL faster than
  extracting the data into a program (for example, Python) and then processing
  it. <em>Documentation link for further reading: <a
  href="https://python-oracledb.readthedocs.io/en/latest/user_guide/plsql_execution.html">PL/SQL
  Execution</a></em>.</p>

  <ul>
    <li>
      <h4>5.1 PL/SQL function</h4>

      <p>The <a href="#installsampleenv">environment setup file</a> has already
      created the new table named <strong>ptab</strong> and a PL/SQL stored
      function <strong>myfunc</strong> to insert a row into
      <strong>ptab</strong> and return double the inserted value. The script to
      create the table and function is below (you do not need to run this):</p>

      <pre>create table ptab (mydata varchar(20), myid number);

create or replace function myfunc(d_p in varchar2, i_p in number) return number as
  begin
    insert into ptab (mydata, myid) values (d_p, i_p);
    return (i_p * 2);
  end;
/</pre>

      <p>The <code>myfunc</code> PL/SQL stored function will be used by the
      <code>plsql_func.py</code> file below.</p>

      <p>Review the code contained in <code>plsql_func.py</code>:</p>

      <pre>
import oracledb
import db_config

con = oracledb.connect(user=db_config.user, password=db_config.pw, dsn=db_config.dsn)
cur = con.cursor()

res = cur.callfunc('myfunc', int, ('abc', 2))
print(res)
</pre>

      <p>This uses the <code>callfunc()</code> method to execute the function.
      The second parameter is the type of the returned value. It should be one
      of the types supported by python-oracledb or one of the type constants
      defined by python-oracledb (such as <em>oracledb.NUMBER</em>). The two
      PL/SQL function parameters are passed as a tuple, binding them to the
      function parameter arguments.</p>

      <p>From a terminal window, run:</p>

      <pre><strong>python plsql_func.py</strong></pre>

      <p>The output is a result of the PL/SQL function calculation.</p>

    </li>

    <li><h4>5.2 PL/SQL procedures</h4>

      <p>The <a href="#installsampleenv">environment setup file</a> has already
      created a PL/SQL stored procedure <code>myproc</code> to accept two
      parameters by running the SQL script below:</p>

      <pre>create or replace procedure myproc(v1_p in number, v2_p out number) as
begin
  v2_p := v1_p * 2;
end;
/</pre>

      <p>The second parameter contains an OUT return value.The
      <code>myproc</code> PL/SQL stored procedure will be used by the
      <code>plsql_proc.py</code> file below.</p>

      <p>Review the code contained in <code>plsql_proc.py</code>:</p>

      <pre>
import oracledb
import db_config

con = oracledb.connect(user=db_config.user, password=db_config.pw, dsn=db_config.dsn)
cur = con.cursor()

myvar = cur.var(int)
cur.callproc('myproc', (123, myvar))
print(myvar.getvalue())</pre>

      <p>This creates an integer variable <code>myvar</code> to hold
      the value returned by the PL/SQL OUT parameter. The input number
      123 and the output variable name are bound to the procedure call
      parameters using a tuple.</p>

      <p>To call the PL/SQL procedure, the <code>callproc()</code>
      method is used.</p>

      <p>In a terminal window, run:</p>

      <pre><strong>python plsql_proc.py</strong></pre>

      <p>The <code>getvalue()</code> method displays the returned
      value.</p>

    </li>
  </ul>

  <h2><a name="handlers">6. Type Handlers</a></h2>

  <p>Type handlers enable applications to alter data that is fetched from, or
  sent to, the database. <em>Documentation links for further reading: <a
  href="https://python-oracledb.readthedocs.io/en/latest/user_guide/sql_execution.html#changing-fetched-data-types-with-output-type-handlers"
  >Changing Fetched Data Types with Output Type Handlers</a> and <a
  href="https://python-oracledb.readthedocs.io/en/latest/user_guide/bind.html#changing-bind-data-types-using-an-input-type-handler"
  >Changing Bind Data Types using an Input Type Handler</a></em>.</p>

  <ul>
    <li>
      <h4>6.1 Basic output type handler</h4>

      <p>Output type handlers enable applications to change how data
      is fetched from the database.  For example, numbers can be
      returned as strings or decimal objects.</p>

      <p>A type handler is enabled by setting the
      <code>outputtypehandler</code> attribute on either a cursor or
      a connection. If set on a cursor, it only affects queries executed
      by that cursor. If set on a connection, it affects all queries executed
      on cursors created by that connection.</p>

      <p>Review the code contained in <code>type_output.py</code>:</p>

      <pre>
import oracledb
import db_config

con = oracledb.connect(user=db_config.user, password=db_config.pw, dsn=db_config.dsn)
cur = con.cursor()

print("Standard output...")
for row in cur.execute("select * from dept"):
    print(row)
</pre>

      <p>In a terminal window, run:</p>

      <pre><strong>python type_output.py</strong></pre>

      <p>This shows the department number represented as digits like
      <code>10</code>.</p>

      <p>Add an output type handler to the bottom of the file:</p>

      <pre>
<strong>def ReturnNumbersAsStrings(cursor, metadata):
    if metadata.type_code is oracledb.DB_TYPE_NUMBER:
        return cursor.var(str, 9, cursor.arraysize)

print("Output type handler output...")
cur = con.cursor()
cur.outputtypehandler = ReturnNumbersAsStrings
for row in cur.execute("select * from dept"):
    print(row)</strong>
</pre>

      <p>This type handler converts any number columns to strings with
      maximum size 9.</p>

      <p>Run the script again:</p>

      <pre><strong>python type_output.py</strong></pre>

      <p>The new output shows the department numbers are now strings
      within quotes like <code>'10'</code>.</p>

    </li>

    <li><h4>6.2 Output type handlers and variable converters</h4>

      <p>When numbers are fetched from the database, the conversion from Oracle's decimal representation to Python's binary format may need careful handling.  To avoid unexpected issues, the  general recommendation is to do number operations in SQL or PL/SQL, or to use the decimal module in Python.</p>

      <p>Output type handlers can be combined with variable converters
      to change how data is fetched.</p>

      <p>Review <code>type_converter.py</code>:</p>

      <pre>
import oracledb
import db_config

con = oracledb.connect(user=db_config.user, password=db_config.pw, dsn=db_config.dsn)
cur = con.cursor()

for value, in cur.execute("select 0.1 from dual"):
    print("Value:", value, "* 3 =", value * 3)
</pre>

      <p>Run the script in a terminal window:</p>

      <pre><strong>python type_converter.py</strong></pre>

      <p>The output is like:</p>

      <pre>Value: 0.1 * 3 = 0.30000000000000004</pre>

      <p>Edit the file and add a type handler that uses a Python decimal converter:</p>

      <pre>import oracledb
<strong>import decimal</strong>
import db_config

con = oracledb.connect(user=db_config.user, password=db_config.pw, dsn=db_config.dsn)
cur = con.cursor()

<strong>def ReturnNumbersAsDecimal(cursor, metadata):
    if metadata.type_code is oracledb.DB_TYPE_NUMBER:
        return cursor.var(str, 9, cursor.arraysize, outconverter=decimal.Decimal)

cur.outputtypehandler = ReturnNumbersAsDecimal</strong>

for value, in cur.execute("select 0.1 from dual"):
    print("Value:", value, "* 3 =", value * 3)
</pre>

      <p>The Python <code>decimal.Decimal</code> converter gets called
      with the string representation of the Oracle number.  The output
      from <code>decimal.Decimal</code> is returned in the output
      tuple.  </p>

      <p>Run the script again:</p>

      <pre><strong>python type_converter.py</strong></pre>

      <p>Output is like:</p>

      <pre>Value: 0.1 * 3 = 0.3</pre>

      <p>The code above demonstrates the use of outconverter, but in this
      particular case, python-oracledb offers a simple convenience attribute to
      do this exact conversion:</p>

    <pre>
import oracledb

oracledb.defaults.fetch_decimals = True
</pre>

     <p>You can also set <em>fetch_decimals</em> as an <code>execute()</code>
     parameter.</p>

  </li>

  <li>
    <h4>6.3 Input type handlers</h4>

    <p>Input type handlers enable applications to change how data is bound to
    statements, or to enable new types to be bound directly without having to
    be converted individually.</p>

    <p>Review <code>type_input.py</code>, which includes a new class and
    converter (shown in bold):</p>

    <pre>
import oracledb
import db_config
import json

con = oracledb.connect(user=db_config.user,
                       password=db_config.pw, dsn=db_config.dsn)
cur = con.cursor()

# Create table
cur.execute("""begin
                 execute immediate 'drop table BuildingTable';
                 exception when others then
                   if sqlcode &lt;&gt; -942 then
                     raise;
                   end if;
               end;""")
cur.execute("""create table BuildingTable (
               ID number(9) not null,
               BuildingDetails varchar2(400),
               constraint TestTempTable_pk primary key (ID))""")

# Create a Python class for a Building
<strong>class Building(object):

    def __init__(self, building_id, description, num_floors):
        self.building_id = building_id
        self.description = description
        self.num_floors = num_floors

    def __repr__(self):
        return "&lt;Building %s: %s&gt;" % (self.building_id, self.description)

    def __eq__(self, other):
        if isinstance(other, Building):
            return other.building_id == self.building_id \
                and other.description == self.description \
                and other.num_floors == self.num_floors
        return NotImplemented

    def to_json(self):
        return json.dumps(self.__dict__)

    @classmethod
    def from_json(cls, value):
        result = json.loads(value)
        return cls(**result)

# Convert a Python building object to SQL JSON type that can be read as a string
def building_in_converter(value):
    return value.to_json()


def input_type_handler(cursor, value, num_elements):
    if isinstance(value, Building):
        return cursor.var(oracledb.DB_TYPE_VARCHAR, arraysize=num_elements,
                          inconverter=building_in_converter)


building = Building(1, "The First Building", 5)  # Python object
cur.execute("truncate table BuildingTable")
cur.inputtypehandler = input_type_handler
cur.execute("insert into BuildingTable (ID, BuildingDetails) values (:1, :2)",
            (building.building_id, building))
con.commit()</strong>

# Query the row
print("Querying the row just inserted...")
cur.execute("select ID, BuildingDetails from BuildingTable")
for (int_col, string_col) in cur:
    print("Building ID:", int_col)
    print("Building Details in JSON format:", string_col)
</pre>

    <p>In the file, a Python class <code>Building</code> is defined to hold
    basic information about a building. The <code>Building</code> class is used
    lower in the code to create a Python instance:</p>

    <pre>
building = Building(1, &quot;The First Building&quot;, 5)</pre>

    <p>which is then directly bound into the INSERT statement like:</p>

    <pre>cur.execute("insert into BuildingTable (ID, BuildingDetails) values (:1, :2)", (building.building_id, building))</pre>

    <p>The mapping between Python and Oracle objects is handled in
    <code>building_in_converter</code> which creates an Oracle character object
    from the <code>Building</code> Python object in a JSON format.  The
    <code>building_in_converter</code> method is called by the input type
    handler <code>input_type_handler</code> whenever an instance of
    <code>Building</code> is inserted with the cursor.</p>

    <p>Run the script in a terminal window:</p>

    <pre><strong>python type_input.py</strong></pre>

    <p>You should see the following output:</p>

    <pre>Querying the row just inserted...
Building ID: 1
Building Details in JSON format: {"building_id": 1, "description": "The First Building", "num_floors": 5}</pre>

    </li>

  </ul>

  <h2><a name="lobs">7. LOBs</a></h2>

  <p>Oracle Database "LOB" long objects can be streamed using a LOB locator, or worked with directly as strings or bytes. <em>Documentation link
for further reading: <a
  href="https://python-oracledb.readthedocs.io/en/latest/user_guide/lob_data.html"
  >Using CLOB and BLOB Data</a></em>.</p>

  <ul>
    <li>
    <h4>7.1 Fetching a CLOB using a locator</h4>

      <p>Review the code contained in <code>clob.py</code>:</p>

<pre>
import oracledb
import db_config

con = oracledb.connect(user=db_config.user, password=db_config.pw, dsn=db_config.dsn)
cur = con.cursor()

print("Inserting data...")
cur.execute("truncate table testclobs")
long_string = ""
for i in range(5):
    char = chr(ord('A') + i)
    long_string += char * 250
    cur.execute("insert into testclobs values (:1, :2)",
                   (i + 1, "String data " + longString + ' End of string'))
con.commit()

print("Querying data...")
cur.execute("select * from testclobs where id = :id", {'id': 1})
(id, clob) = cur.fetchone()
print("CLOB length:", clob.size())
clobdata = clob.read()
print("CLOB data:", clobdata)
</pre>

      <p>This inserts some test string data and then fetches one
      record into <code>clob</code>, which is a python-oracledb character
      LOB Object.  Methods on LOB include <code>size()</code> and
      <code>read()</code>.</p>

      <p>To see the output, run the script in a terminal window:</p>

      <pre><strong>python clob.py</strong></pre>

      <p>Edit the file and experiment reading chunks of data by giving start
      character position and length, such as <code>clob.read(1,10)</code>.</p>

    </li>

    <li>
      <h4>7.2 Fetching a CLOB as a string</h4>

      <p>For CLOBs small enough to fit in the application memory, it
      is much faster to fetch them directly as strings.</p>

      <p>Review the code contained in <code>clob_string.py</code>. The
      differences from <code>clob.py</code> are shown in bold:</p>

      <pre>
import oracledb
import db_config

<strong>oracledb.defaults.fetch_lobs = False</strong>

con = oracledb.connect(user=db_config.user, password=db_config.pw, dsn=db_config.dsn)
cur = con.cursor()

print("Inserting data...")
cur.execute("truncate table testclobs")
long_string = ""
for i in range(5):
    char = chr(ord('A') + i)
    long_string += char * 250
    cur.execute("insert into testclobs values (:1, :2)",
                (i + 1, "String data " + long_string + ' End of string'))
con.commit()

print("Querying data...")
cur.execute("select * from testclobs where id = :id", {'id': 1})
<strong>(id, clobdata) = cur.fetchone()
print("CLOB length:", len(clobdata))
print("CLOB data:", clobdata)</strong>
</pre>

      <p>Setting <em>oracledb.defaults.fetch_lobs</em> to False causes
      python-oracledb to fetch the CLOB as a string. You can alternatively set
      <em>fetch_lobs</em> as an <code>execute()</code> parameter. Standard
      Python string functions such as <code>len()</code> can be used on the
      result.</p>

      <p>The output is the same as for <code>clob.py</code>.  To check, run the
      script:</p>

      <pre><strong>python clob_string.py</strong></pre>

    </li>
  </ul>

  <h2><a name="json">8. JSON</a></h2>

  <p>Oracle Database supports a JSON data type and also has powerful
  functionality to work with relational data as if it were
  JSON. <em>Documentation link for further reading: <a
  href="https://python-oracledb.readthedocs.io/en/latest/user_guide/json_data_type.html">Using
  JSON Data</a></em>.</p>

  <ul>
    <li><h4>8.1 Inserting JSON</h4>

      <p>Review the code contained in <code>json_insert.py</code>:</p>

      <pre>import oracledb
import db_config

con = oracledb.connect(
    user=db_config.user, password=db_config.pw, dsn=db_config.dsn
)
cur = con.cursor()

data = dict(name="Rod", dept="Sales", location="Germany")
inssql = "insert into jtab (id, json_data) values (:1, :2)"
cur.setinputsizes(None, oracledb.DB_TYPE_JSON)
cur.execute(inssql, [101, data])
</pre>

      <p>This inserts the Python dictionary into the <code>json_data</code>
      column which is of type JSON.</p>

      <p>The <code>setinputsizes()</code> call specifies that binding the first
      bind placeholder <code>id</code> (the value <code>101</code>) should use
      default numeric binding, and that the value for the second bind
      placeholder (the dictionary <code>data</code>) will be treated as JSON
      data.</p>

    </li>

    <li><h4>8.2 Fetching JSON</h4>

      <p>To fetch JSON, edit <code>json_insert.py</code> and add this at the
      bottom:</p>

      <pre>sql = "select c.json_data from jtab c"
for (j,) in cur.execute(sql):
    print(j)
</pre>

      <p>Run the script in a terminal window:</p>

      <pre><strong>python json_insert.py</strong></pre>

      <p>The inserted data is displayed.  You can experiment with inserting
      different dictionaries.</p>

      <p>If you want to extract parts of a JSON document, you can use Oracle
      Database "dot notation".</p>

      <p>Edit <code>json_insert.py</code> and add this at the
      bottom:</p>

      <pre>sql = """select c.json_data.location
         from jtab c
         offset 0 rows fetch next 1 rows only"""
for (j,) in cur.execute(sql):
    print(j)
</pre>

      <p>Run the script in a terminal window:</p>

      <pre><strong>python json_insert.py</strong></pre>

      <p>Only the location of the inserted data is shown.</p>

      <p>Refer to the <a
      href="https://docs.oracle.com/en/database/oracle/oracle-database/23/adjsn/index.html"
      >Database JSON Developer's Guide</a> for more information about Oracle's
      JSON support.</p>

    </li>
  </ul>

  <h2><a name="vector">9. VECTORs</a></h2>

  <p>Oracle Database version 23 supports a VECTOR data type for artificial
  intelligence and machine learning search operations. <em>Documentation link
  for further reading: <a
  href="https://python-oracledb.readthedocs.io/en/latest/user_guide/vector_data_type.html"
  >Using VECTOR Data</a></em>.</p>

  <p>This section uses the table <em>vtab</em> created as:</p>

<pre>
create table vtab (
  id number(9) not null primary key,
  v64 vector(3, float64));
</pre>

  <p>The column <em>v64</em> represents a dense vector of dimension 3 which
  stores 64-bit floating points numbers.  Oracle Database also supports 8-bit
  signed integers, 8-bit unsigned integers, and 32-bit floating point number
  vector formats. Vectors can be dense or sparse.</p>

<ul>
  <li>
    <h4>9.1 Inserting a VECTOR</h4>

    <p>Review the code contained in <code>vector.py</code>:</p>

    <pre>import array
import oracledb
import db_config

con = oracledb.connect(
    user=db_config.user, password=db_config.pw, dsn=db_config.dsn
)
cur = con.cursor()

vector_data_64 = array.array("d", [11.25, 11.75, 11.5])

cur.execute(
    "insert into vtab (id, v64) values (:1, :2)", [101, vector_data_64],
)</pre>

    <p>Python-oracledb uses the Python <em>array.array()</em> type to represent
    vectors by default.</p>

  </li>

  <li>
    <h4>9.2 Fetching a VECTOR</h4>

    <p>Edit <code>vector.py</code> and add this code at the bottom:</p>

    <pre>cur.execute("select v64 from vtab")

for (v,) in cur:
    print(v)
    print(type(v))
</pre>

    <p>Run the script in a terminal window:</p>

    <pre><strong>python vector.py</strong></pre>

    <p>This displays the inserted vector.</p>

    <p>Each non-sparse vector is represented as an <em>array.array</em> type.
    Sparse vectors (not shown) would be represented as
    <em>oracledb.SparseVector()</em> instances.  </p>

  </li>

  <li>
    <h4>9.3 Working with VECTORs and NumPy</h4>

    <p>The NumPy package is one of the popular libraries for data analysis. It
    is simple to use with the Oracle Database VECTOR data type.</p>

    <p>To run the example in this section, the package needs to be
    installed:</p>

    <pre>python -m pip install numpy --upgrade</pre>

    <p>Review the code contained in <code>vector_numpy.py</code>:</p>

<pre>
import array
import numpy
import oracledb
import db_config

con = oracledb.connect(
    user=db_config.user, password=db_config.pw, dsn=db_config.dsn
)
cur = con.cursor()

vector_data_64 = numpy.array([11.25, 11.75, 11.5], dtype=numpy.float64)

cur.execute(
    "insert into vtab (id, v64) values (:1, :2)", [202, vector_data_64],
)

for (v,) in cur.execute("select v64 from vtab"):
    print(v)
    print(type(v))
</pre>

    <p>As it is, this will not run because the NumPy data needs to be converted
    to a Python array for python-oracledb.</p>

    <p>Edit <code>vector_numpy.py</code> and insert an inconverter and an input
    type handler to convert the NumPy data into the correct format for
    insertion.</p>

    <p>To convert queried vectors into NumPy format, also add an outconverter
    and an output type handler:</p>

    <pre>import array
import numpy
import oracledb
import db_config

con = oracledb.connect(
    user=db_config.user, password=db_config.pw, dsn=db_config.dsn
)
cur = con.cursor()

def numpy_converter_in(value):
    if value.dtype == numpy.float64:
        dtype = "d"
    elif value.dtype == numpy.float32:
        dtype = "f"
    elif value.dtype == numpy.uint8:
        dtype = "B"
    else:
        dtype = "b"
    return array.array(dtype, value)

def input_type_handler(cur, value, arraysize):
    if isinstance(value, numpy.ndarray):
        return cur.var(
            oracledb.DB_TYPE_VECTOR,
            arraysize=arraysize,
            inconverter=numpy_converter_in,
        )

con.inputtypehandler = input_type_handler

def numpy_converter_out(value):
    return numpy.array(value, copy=False, dtype=value.typecode)

def output_type_handler(cur, metadata):
    if metadata.type_code is oracledb.DB_TYPE_VECTOR:
        return cur.var(
            metadata.type_code,
            arraysize=cur.arraysize,
            outconverter=numpy_converter_out,
        )

con.outputtypehandler = output_type_handler

vector_data_64 = numpy.array([11.25, 11.75, 11.5], dtype=numpy.float64)

cur.execute(
    "insert into vtab (id, v64) values (:1, :2)", [202, vector_data_64],
)

for (v,) in cur.execute("select v64 from vtab"):
    print(v)
    print(type(v))
</pre>

<p>Run the script in a terminal window:</p>

<pre><strong>python vector_numpy.py</strong></pre>

<p>Each vector queried is represented as a <em>numpy.ndarray</em> type.</p>

  </li>
</ul>

<h2><a name="rowfactory">10. Rowfactory functions</a></h2>

  <p>Rowfactory functions enable queries to return objects other than
  tuples.  They can be used to provide names for the various columns
  or to return custom objects.</p>

  <ul>
    <li><h4>10.1 Rowfactory for mapping column names</h4>

  <p>Review the code contained in <code>rowfactory.py</code>:</p>

<pre>
import collections
import oracledb
import db_config

con = oracledb.connect(user=db_config.user, password=db_config.pw, dsn=db_config.dsn)
cur = con.cursor()

cur.execute("select deptno, dname from dept")
rows = cur.fetchall()

print('Array indexes:')
for row in rows:
    print(row[0], "->", row[1])

print('Loop target variables:')
for c1, c2 in rows:
    print(c1, "->", c2)
</pre>

  <p>This shows two methods of accessing result set items from a data row.  The
  first uses array indexes like <code>row[0]</code>. The second uses loop
  target variables that take each row tuple's values.</p>

  <p>Run the script in a terminal window:</p>

  <pre><strong>python rowfactory.py</strong></pre>

  <p>Both access methods gives the same results.</p>

  <p>To use a rowfactory function, edit <code>rowfactory.py</code> and
  add this code at the bottom:</p>

<pre>
<strong>print('Rowfactory:')
cur.execute("select deptno, dname from dept")
cur.rowfactory = collections.namedtuple("MyClass", ["DeptNumber", "DeptName"])

rows = cur.fetchall()
for row in rows:
    print(row.DeptNumber, "->", row.DeptName)
</strong></pre>

      <p>This uses the Python factory function <code>namedtuple()</code> to
      create a subclass of tuple that allows access to the elements via indexes
      or the given field names.</p>

      <p>The <code>print()</code> function shows the use of the new
      named tuple fields.  This coding style can help reduce coding
      errors.</p>

      <p>Run the script again:</p>

<pre><strong>python rowfactory.py</strong></pre>


  <p>The output results are the same.</p>

</li>
</ul>

<h2><a name="subclass">11. Subclassing connections and cursors</a></h2>

  <p>Subclassing enables application to "hook" connection and cursor creation.
  This can be used to alter or log connection and execution parameters, and to
  extend python-oracledb functionality. <em>Documentation link for further
  reading: <a
  href="https://python-oracledb.readthedocs.io/en/latest/user_guide/tracing.html#application-tracing"
  >Application Tracing</a></em>.</p>

  <ul>
    <li><h4>11.1 Subclassing connections</h4>

  <p>Review the code contained in <code>subclass.py</code>:</p>

  <pre>
import oracledb
import db_config

class MyConnection(oracledb.Connection):

    def __init__(self):
        print("Connecting to database")
        return super(MyConnection, self).__init__(user=db_config.user, password=db_config.pw, dsn=db_config.dsn)

con = MyConnection()
cur = con.cursor()

cur.execute("select count(*) from emp where deptno = :bv", (10,))
count, = cur.fetchone()
print("Number of rows:", count)
</pre>

  <p>This creates a new class "MyConnection" that inherits from the
  python-oracledb Connection class.  The <code>__init__</code> method is
  invoked when an instance of the new class is created.  It prints a message
  and calls the base class, passing the connection credentials.</p>

  <p>In the "normal" application, the application code:</p>

  <pre>con = MyConnection()</pre>

  <p>does not need to supply any credentials, as they are embedded in the
  custom subclass. All the python-oracledb methods such as
  <code>cursor()</code> are available, as shown by the query.</p>

  <p>Run the script in a terminal window:</p>

<pre><strong>python subclass.py</strong></pre>

  <p>The query executes successfully.</p>

    </li>

    <li><h4>11.2 Subclassing cursors</h4>

      <p>Edit <code>subclass.py</code> and extend the
      <code>cursor()</code> method with a new MyCursor class:</p>

<pre>
import oracledb
import db_config

class MyConnection(oracledb.Connection):

    def __init__(self):
        print("Connecting to database")
        return super(MyConnection, self).__init__(user=db_config.user, password=db_config.pw, dsn=db_config.dsn)

<strong>    def cursor(self):
        return MyCursor(self)

class MyCursor(oracledb.Cursor):

   def execute(self, statement, args):
       print("Executing:", statement)
       print("Arguments:")
       for argIndex, arg in enumerate(args):
           print("  Bind", argIndex + 1, "has value", repr(arg))
           return super(MyCursor, self).execute(statement, args)

   def fetchone(self):
       print("Fetchone()")
       return super(MyCursor, self).fetchone()</strong>

con = MyConnection()
cur = con.cursor()

cur.execute("select count(*) from emp where deptno = :bv", (10,))
count, = cur.fetchone()
print("Number of rows:", count)
</pre>

<p>When the application gets a cursor from the <code>MyConnection</code> class,
the new <code>cursor()</code> method returns an instance of our new
<code>MyCursor</code> class.</p>

<p>The "application" query code remains unchanged.  The new
<code>execute()</code> and <code>fetchone()</code> methods of the
<code>MyCursor</code> class get invoked.  They do some logging and invoke the
parent methods to do the actual statement execution.</p>

<p>To confirm this, run the script again:</p>

<pre><strong>python subclass.py</strong></pre>

    </li>

</ul>

<h2><a name="bindnamedobj">10 Binding named objects</a></h2>

  <p>Python-oracledb can fetch and bind named object types such as Oracle's
  Spatial Data Objects (SDO).</p>

  <p>The SDO definition includes the following attributes:</p>

<pre>
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 SDO_GTYPE                                          NUMBER
 SDO_SRID                                           NUMBER
 SDO_POINT                                          MDSYS.SDO_POINT_TYPE
 SDO_ELEM_INFO                                      MDSYS.SDO_ELEM_INFO_ARRAY
 SDO_ORDINATES                                      MDSYS.SDO_ORDINATE_ARRAY
</pre>

<ul>
    <li>
      <h4>12.1 How to bind named objects</h4>

      <p>Review the code contained in <code>bind_sdo.py</code>:</p>

<pre>
import oracledb
import db_config

con = oracledb.connect(user=db_config.user, password=db_config.pw, dsn=db_config.dsn)
cur = con.cursor()

# Create table
cur.execute("""begin
                 execute immediate 'drop table testgeometry';
                 exception when others then
                   if sqlcode &lt;&gt; -942 then
                     raise;
                   end if;
               end;""")
cur.execute("""create table testgeometry (
               id number(9) not null,
               geometry MDSYS.SDO_GEOMETRY not null)""")

# Create and populate Oracle objects
type_obj = con.<strong>gettype</strong>("MDSYS.SDO_GEOMETRY")
element_info_type_obj = con.<strong>gettype</strong>("MDSYS.SDO_ELEM_INFO_ARRAY")
ordinate_type_obj = con.<strong>gettype</strong>("MDSYS.SDO_ORDINATE_ARRAY")
obj = type_obj.<strong>newobject()</strong>
obj.SDO_GTYPE = 2003
obj.SDO_ELEM_INFO = element_info_type_obj.<strong>newobject()</strong>
obj.SDO_ELEM_INFO.<strong>extend</strong>([1, 1003, 3])
obj.SDO_ORDINATES = ordinate_type_obj.<strong>newobject()</strong>
obj.SDO_ORDINATES.<strong>extend</strong>([1, 1, 5, 7])
print("Created object", obj)

# Add a new row
print("Adding row to table...")
cur.execute("insert into testgeometry values (1, :objbv)", objbv = obj)
print("Row added!")

# Query the row
print("Querying row just inserted...")
cur.execute("select id, geometry from testgeometry");
for row in cur:
    print(row)</pre>

<p>This uses <code>gettype()</code> to get the database types of the SDO
and its object attributes. The <code>newobject()</code> calls create
Python representations of those objects. The python object attributes are
then set.  Oracle VARRAY types such as SDO_ELEM_INFO_ARRAY are set with
<code>extend()</code>.</p>

<p>Run the script in a terminal window:</p>

<pre><strong>python bind_sdo.py</strong></pre>

<p>The new SDO is shown as an object, similar to </p>

<pre>(1, &lt;oracledb.Object MDSYS.SDO_GEOMETRY at 0x104a76230&gt;)</pre>

<p>To show the attribute values, edit the query code section at the end of the
file.  Add a new method that traverses the object. The file below the existing
comment "<code># (Change below here)</code>") should look like:</p>

<pre>
# (Change below here)

# Define a function to dump the contents of an Oracle object
def dumpobject(obj, prefix = "  "):
    if obj.type.iscollection:
        print(prefix, "[")
        for value in obj.aslist():
            if isinstance(value, oracledb.Object):
                dumpobject(value, prefix + "  ")
            else:
                print(prefix + "  ", repr(value))
        print(prefix, "]")
    else:
        print(prefix, "{")
        for attr in obj.type.attributes:
            value = getattr(obj, attr.name)
            if isinstance(value, oracledb.Object):
                print(prefix + "  " + attr.name + " :")
                dumpobject(value, prefix + "    ")
            else:
                print(prefix + "  " + attr.name + " :", repr(value))
        print(prefix, "}")

# Query the row
print("Querying row just inserted...")
cur.execute("select id, geometry from testgeometry")
for id, obj in cur:
    print("Id: ", id)
    dumpobject(obj)</pre>

<p>Run the script again:</p>

<pre><strong>python bind_sdo.py</strong></pre>

<p>This shows</p>
<pre>
Querying row just inserted...
Id:  1
   {
    SDO_GTYPE : 2003
    SDO_SRID : None
    SDO_POINT : None
    SDO_ELEM_INFO :
       [
         1
         1003
         3
       ]
    SDO_ORDINATES :
       [
         1
         1
         5
         7
       ]
   }
</pre>

<p>To explore further, try setting the SDO attribute SDO_POINT, which is of
type SDO_POINT_TYPE.</p>

<p>The <code>gettype()</code> and <code>newobject()</code> methods can also be
used to bind PL/SQL Records and Collections.</p>

<p>Before deciding to use objects, review your performance goals because
working with scalar values can be faster.</p>

   </li>
</ul>

<h2><a name="typehandlers">13. Input and Output Type Handlers with named objects</a></h2>

<p><em>Documentation links for further reading: <a
href="https://python-oracledb.readthedocs.io/en/latest/user_guide/sql_execution.html#changing-fetched-data-types-with-output-type-handlers"
>Changing Fetched Data Types with Output Type Handlers</a> and <a
href="https://python-oracledb.readthedocs.io/en/latest/user_guide/bind.html#changing-bind-data-types-using-an-input-type-handler"
>Changing Bind Data Types using an Input Type Handler</a></em>.</p>

<ul>
  <li>
  <h4>13.1 Input type handlers with named objects</h4>

    <p>Input type handlers for named objects can enable applications to change
    how data is bound to the individual attributes of the named objects. Review
    the code contained in <code>type_input_named_obj.py</code>, which is
    similar to the final <code>bind_sdo.py</code> from section 12.1, with the
    addition of a new class and converter (shown in bold):</p>

<pre>
import oracledb
import db_config

con = oracledb.connect(user=db_config.user, password=db_config.pw, dsn=db_config.dsn)
cur = con.cursor()

# Create table
cur.execute("""begin
                 execute immediate 'drop table testgeometry';
                 exception when others then
                   if sqlcode &lt;&gt; -942 then
                     raise;
                   end if;
               end;""")
cur.execute("""create table testgeometry (
               id number(9) not null,
               geometry MDSYS.SDO_GEOMETRY not null)""")

<strong># Create a Python class for an SDO
class mySDO(object):
    def __init__(self, gtype, elemInfo, ordinates):
        self.gtype = gtype
        self.elemInfo = elemInfo
        self.ordinates = ordinates</strong>

# Get Oracle type information
obj_type = con.gettype("MDSYS.SDO_GEOMETRY")
element_info_type_obj = con.gettype("MDSYS.SDO_ELEM_INFO_ARRAY")
ordinate_type_obj = con.gettype("MDSYS.SDO_ORDINATE_ARRAY")

# Convert a Python object to MDSYS.SDO_GEOMETRY
<strong>def SDOInConverter(value):
    obj = obj_type.newobject()
    obj.SDO_GTYPE = value.gtype
    obj.SDO_ELEM_INFO = element_info_type_obj.newobject()
    obj.SDO_ELEM_INFO.extend(value.elemInfo)
    obj.SDO_ORDINATES = ordinate_type_obj.newobject()
    obj.SDO_ORDINATES.extend(value.ordinates)
    return obj

def SDOInputTypeHandler(cursor, value, numElements):
    if isinstance(value, mySDO):
        return cursor.var(oracledb.OBJECT, arraysize=numElements,
                inconverter=SDOInConverter, typename=obj_type.name)</strong>

sdo = mySDO(2003, [1, 1003, 3], [1, 1, 5, 7])  # Python object
<strong>cur.inputtypehandler = SDOInputTypeHandler</strong>
cur.execute("insert into testgeometry values (:1, :2)", (1, sdo))

# Define a function to dump the contents of an Oracle object
def dumpobject(obj, prefix = "  "):
    if obj.type.iscollection:
        print(prefix, "[")
        for value in obj.aslist():
            if isinstance(value, oracledb.Object):
                dumpobject(value, prefix + "  ")
            else:
                print(prefix + "  ", repr(value))
        print(prefix, "]")
    else:
        print(prefix, "{")
        for attr in obj.type.attributes:
            value = getattr(obj, attr.name)
            if isinstance(value, oracledb.Object):
                print(prefix + "  " + attr.name + " :")
                dumpobject(value, prefix + "    ")
            else:
                print(prefix + "  " + attr.name + " :", repr(value))
        print(prefix, "}")

# Query the row
print("Querying row just inserted...")
cur.execute("select id, geometry from testgeometry")
for (id, obj) in cur:
    print("Id: ", id)
    dumpobject(obj)
</pre>

<p>The mapping between Python and Oracle objects is handled in
<code>SDOInConverter</code> which uses the python-oracledb
<code>newobject()</code> and <code>extend()</code> methods to create an Oracle
object from the Python object values. The <code>SDOInConverter</code> method is
called by the input type handler <code>SDOInputTypeHandler</code> whenever an
instance of <code>mySDO</code> is inserted with the cursor.</p>

<p>Run the script in a terminal window:</p>

<pre><strong>python type_input_named_obj.py</strong></pre>

<p>This will show:</p>

<pre>Querying row just inserted...
Id:  1
   {
    SDO_GTYPE : 2003
    SDO_SRID : None
    SDO_POINT : None
    SDO_ELEM_INFO :
       [
         1
         1003
         3
       ]
    SDO_ORDINATES :
       [
         1
         1
         5
         7
       ]
   }</pre></li>
</ul>

<ul>

<li>
  <h4>13.2 Output type handlers with named objects</h4>

  <p>Output type handlers enable applications to extract the data from database
  named objects into a user-defined Python object (defined by the
  <code>mySDO</code> class here). Review the code contained in
  <code>type_output_named_obj.py</code> with the output converter function
  shown in bold:</p>

<pre>
import oracledb
import db_config

con = oracledb.connect(user=db_config.user,
                       password=db_config.pw, dsn=db_config.dsn)
cur = con.cursor()

# Create table
cur.execute("""begin
                 execute immediate 'drop table testgeometry';
                 exception when others then
                   if sqlcode &lt;&gt; -942 then
                     raise;
                   end if;
               end;""")
cur.execute("""create table testgeometry (
               id number(9) not null,
               geometry MDSYS.SDO_GEOMETRY not null)""")

# Create a Python class for an SDO
class mySDO(object):
    def __init__(self, gtype, elemInfo, ordinates):
        self.gtype = gtype
        self.elemInfo = elemInfo
        self.ordinates = ordinates

# Get Oracle type information
obj_type = con.gettype("MDSYS.SDO_GEOMETRY")
element_info_type_obj = con.gettype("MDSYS.SDO_ELEM_INFO_ARRAY")
ordinate_type_obj = con.gettype("MDSYS.SDO_ORDINATE_ARRAY")

# Convert a Python object to MDSYS.SDO_GEOMETRY
def SDOInConverter(value):
    obj = obj_type.newobject()
    obj.SDO_GTYPE = value.gtype
    obj.SDO_ELEM_INFO = element_info_type_obj.newobject()
    obj.SDO_ELEM_INFO.extend(value.elemInfo)
    obj.SDO_ORDINATES = ordinate_type_obj.newobject()
    obj.SDO_ORDINATES.extend(value.ordinates)
    return obj

def SDOInputTypeHandler(cursor, value, numElements):
    if isinstance(value, mySDO):
        return cursor.var(oracledb.OBJECT, arraysize=numElements,
                          inconverter=SDOInConverter, typename=obj_type.name)

# Convert a MDSYS.SDO_GEOMETRY DB Object to Python object
<strong>def SDOOutConverter(DBobj):
    return mySDO(int(DBobj.SDO_GTYPE), DBobj.SDO_ELEM_INFO.aslist(),
                 DBobj.SDO_ORDINATES.aslist())</strong>

<strong>def SDOOutputTypeHandler(cursor, metadata):
    if metadata.type_code is oracledb.DB_TYPE_OBJECT:
        return cursor.var(metadata.type, arraysize=cursor.arraysize,
                          outconverter=SDOOutConverter)</strong>

sdo = mySDO(2003, [1, 1003, 3], [1, 1, 5, 7])  # Python object
cur.inputtypehandler = SDOInputTypeHandler
cur.execute("insert into testgeometry values (:1, :2)", (1, sdo))
cur.outputtypehandler = SDOOutputTypeHandler

# Query the SDO Table row
print("Querying the Spatial Data Object(SDO) Table using the Output Type Handler...")
print("----------------------------------------------------------------------------")
cur.execute("select id, geometry from testgeometry")
for (id, obj) in cur:
    print("SDO ID:", id)
    print("SDO GYTPE:", obj.gtype)
    print("SDO ELEMINFO:", obj.elemInfo)
    print("SDO_ORDINATES:", obj.ordinates)</pre>

<p>Note that the Input Type Handler and the InConverter functions are the same
as the previous example. </p>

<p>The mapping between the Python and Oracle objects is handled in
<code>SDOOutConverter</code>. The <code>SDOOutConverter</code> method is called
by the output type handler <code>SDOOutputTypeHandler</code> whenever data of
the named object (<code>MDSYS.SDOGEOMETRY</code> in this case) is selected with
the cursor and needs to be converted to a user-defined Python object
(<code>mySDO</code> object in this case).</p>

<p>Run the script in a terminal window:</p>

<pre><strong>python type_output_named_obj.py</strong></pre>

<p>This will show:</p>

<pre>Querying the Spatial Data Object(SDO) Table using the Output Type Handler...
----------------------------------------------------------------------------
SDO ID: 1
SDO GYTPE: 2003
SDO ELEMINFO: [1, 1003, 3]
SDO_ORDINATES: [1, 1, 5, 7]
</pre>

</li>
</ul>

<h2><a name="aq">14. Advanced Queuing</a></h2>

<p>Oracle Advanced Queuing (AQ) APIs allow messages to be passed between
applications. <em>Documentation link for further reading: <a
href="https://python-oracledb.readthedocs.io/en/latest/user_guide/aq.html"
>Using Oracle Transactional Event Queues and Advanced Queuing</a></em>.</p>

  <ul>
    <li>
    <h4>14.1 Message passing with Oracle Advanced Queuing</h4>

    <p>Review the code contained in <code>aq.py</code>:</p>

<pre>
import oracledb
import decimal
import db_config

con = oracledb.connect(user=db_config.user, password=db_config.pw, dsn=db_config.dsn)
cur = con.cursor()

BOOK_TYPE_NAME = "UDT_BOOK"
QUEUE_NAME = "BOOKS"
QUEUE_TABLE_NAME = "BOOK_QUEUE_TABLE"

# Cleanup
cur.execute(
    """begin
         dbms_aqadm.stop_queue('""" + QUEUE_NAME + """');
         dbms_aqadm.drop_queue('""" + QUEUE_NAME + """');
         dbms_aqadm.drop_queue_table('""" + QUEUE_TABLE_NAME + """');
         execute immediate 'drop type """ + BOOK_TYPE_NAME + """';
         exception when others then
           if sqlcode &lt;&gt; -24010 then
             raise;
           end if;
       end;""")

# Create a type
print("Creating books type UDT_BOOK...")
cur.execute("""
        create type %s as object (
            title varchar2(100),
            authors varchar2(100),
            price number(5,2)
        );""" % BOOK_TYPE_NAME)

# Create queue table and queue and start the queue
print("Creating queue table...")
cur.callproc("dbms_aqadm.create_queue_table",
        (QUEUE_TABLE_NAME, BOOK_TYPE_NAME))
cur.callproc("dbms_aqadm.create_queue", (QUEUE_NAME, QUEUE_TABLE_NAME))
cur.callproc("dbms_aqadm.start_queue", (QUEUE_NAME,))

books_type = con.gettype(BOOK_TYPE_NAME)
queue = con.queue(QUEUE_NAME, booksType)

# Enqueue a few messages
print("Enqueuing messages...")

BOOK_DATA = [
    ("The Fellowship of the Ring", "Tolkien, J.R.R.", decimal.Decimal("10.99")),
    ("Harry Potter and the Philosopher's Stone", "Rowling, J.K.",
            decimal.Decimal("7.99"))
]

for title, authors, price in BOOK_DATA:
    book = books_type.newobject()
    book.TITLE = title
    book.AUTHORS = authors
    book.PRICE = price
    print(title)
    queue.enqone(con.msgproperties(payload=book))
    con.commit()

# Dequeue the messages
print("\nDequeuing messages...")
queue.deqoptions.wait = oracledb.DEQ_NO_WAIT
while True:
    props = queue.deqone()
    if not props:
        break
    print(props.payload.TITLE)
    con.commit()

print("\nDone.")
</pre>

<p>This file sets up Advanced Queuing using Oracle's DBMS_AQADM package.  The
queue is used for passing Oracle UDT_BOOK objects.</p>

<p>Run the script in a terminal window:</p>

<pre><strong>python aq.py</strong></pre>

<p>The output shows messages being queued and dequeued.</p>

<p>To experiment, split the code into three files: one to create and start the
queue and two other files to queue and dequeue messages.  Experiment with
running the queue and dequeue files concurrently in separate terminal
windows.</p>

<p>Try removing the <code>commit()</code> call in <code>aq-dequeue.py</code>.
Now run <code>aq-enqueue.py</code> once and then <code>aq-dequeue.py</code>
several times.  The same messages will be available each time you try to
dequeue them.</p>

<p>Change <code>aq-dequeue.py</code> to commit in a separate transaction by
changing the "visibility" setting:</p>

<pre>
queue.deqoptions.visibility = oracledb.DEQ_IMMEDIATE
</pre>

<p>This gives the same behavior as the original code.</p>

<p>Now change the options of enqueued messages so that they expire from the
queue if they have not been dequeued after four seconds:</p>

<pre>
queue.enqone(con.msgproperties(payload=book, expiration=4))
</pre>

<p>Now run <code>aq-enqueue.py</code> and wait four seconds before you run
<code>aq-dequeue.py</code>.  There should be no messages to dequeue. </p>

<p>If you are stuck, look in the <code>solutions</code> directory at the
<code>aq-dequeue.py</code>, <code>aq-enqueue.py</code> and
<code>aq-queuestart.py</code> files.</p>

</li>
</ul>

 <h2><a name = "scrollable">15. Scrollable cursors</a></h2>

    <p>Scrollable cursors enable python-oracledb applications to move backwards
    as well as forwards in query results. They can be used to skip rows as well
    as move to a particular row. <em>Documentation link for further reading: <a
    href="https://python-oracledb.readthedocs.io/en/latest/user_guide/sql_execution.html#scrollable-cursors"
    >Scrollable Cursors</a></em>.</p>

    <ul>
      <li><h4>15.1 Working with scrollable cursors</h4>

        <p>Review the code contained in <code>query_scroll.py</code>:</p>

        <pre>
import oracledb
import db_config

con = oracledb.connect(user=db_config.user, password=db_config.pw, dsn=db_config.dsn)
cur = con.cursor(<strong>scrollable=True</strong>)

cur.execute("select * from dept order by deptno")

cur.scroll(2, mode="absolute")  # go to second row
print(cur.fetchone())

cur.scroll(-1)                    # go back one row
print(cur.fetchone())
</pre>

    <p>Run the script in a terminal window:</p>

  <pre><strong>python query_scroll.py</strong></pre>

    <p>Edit <code>query_scroll.py</code> and experiment with different
    scroll options and orders, such as:</p>

    <pre>cur.scroll(1)  # go to next row
print(cur.fetchone())

cur.scroll(mode="first")  # go to first row
print(cur.fetchone())</pre>

    <p>Try some scroll options that go beyond the number of rows in the
    resultset.</p>

</li>
</ul>

<h2><a name="dataframes">16. DataFrames</a></h2>

<p>Python-oracledb has a DataFrame class that exposes an Apache Arrow PyCapsule
interface.  Data can be fetched directly from Oracle Database into DataFrame
instances for efficient use with Python libraries such as Apache PyArrow,
Pandas, Polars, NumPy, PyTorch, or to write files in Apache Parquet
format. Data that is already in a third-party library DataFrame instance that
exposes the PyCapsule interface can be inserted into Oracle Database by passing
the instance directly to <code>executemany()</code>.  <em>Documentation link
for further reading: <a
href="https://python-oracledb.readthedocs.io/en/latest/user_guide/sql_execution.html#fetching-data-frames"
>Fetching Data Frames</a></em>.</p>

<p>This section shows how to efficiently fetch data for use with Pandas, modify
it, and then insert it back into Oracle Database. The <em>pyarrow</em> and
<em>pandas</em> packages need to be installed:</p>

      <pre>python -m pip install pyarrow pandas --upgrade</pre>

<ul>
  <li><h4>16.1 Fetching DataFrames</h4>

    <p>Review the code contained in <code>query_pandas.py</code>:</p>

   <pre>
import pandas
import pyarrow
import oracledb
import db_config

con = oracledb.connect(
    user=db_config.user, password=db_config.pw, dsn=db_config.dsn
)

odf = con.fetch_df_all(
    statement="select sal from emp order by empno",
    arraysize=100
)
</pre>

    <p>This uses <code>fetch_df_all()</code> to directly fetch rows into a
    dataframe that internally exposes a PyCapsule interface. For large result
    sets you can tune the arraysize parameter, or use an iterator from the
    <code>fetch_df_batches()</code> method.</p>

    <p>To use the new dataframe in Pandas, edit <code>query_pandas.py</code>
    and add this code at the bottom:</p>

    <pre>
# Get a Pandas DataFrame from the data
df = pyarrow.table(odf).to_pandas()

# Perform various operations on the Pandas DataFrame

print("\nSum:")
print(df.sum())

print("\nMedian:")
print(df.median())
</pre>

    <p>This uses PyArrow functionality to convert the python-oracledb DataFrame
    to a Pandas DataFrame. Pandas sum and median operations are then
    performed.</p>

    <p>Run the script in a terminal window:</p>

    <pre><strong>python query_pandas.py</strong></pre>

    <p>The output is the expected calculations on the employee salary data.</p>

  </li>

  <li><h4>16.2 Inserting DataFrames</h4>

    <p>Python-oracledb DataFrame instances, and instances of DataFrames from
    third-party libraries that support the Apache Arrow PyCapsule Interface
    can be inserted into Oracle Database by passing them directly to
    <code>executemany()</code>.</p>

    <p>Edit <code>query_pandas.py</code> and add this code at the bottom:</p>

    <pre>
# Double everyone's salary and insert the Pandas DataFrame into Oracle Database

df = df * 2

cur = con.cursor()
cur.executemany("insert into pdtab (sal) values (:1)", df)

# Check the inserted data

print("\nNew Salaries")
cur.execute("select * from pdtab")
res = cur.fetchall()
print(res)
</pre>

    <p>Run the script in a terminal window:</p>

    <pre><strong>python query_pandas.py</strong></pre>

    <p>This increases everyone's salary in the Pandas DataFrame and then
    inserts the DataFrame into Oracle Database. The new salaries are queried
    back to confirm the insertion was successful.</p>

    <p>You can also insert DataFrames using Oracle Database Direct Path Loads,
    as shown in a later section.</p> </li>

</ul>

<h2><a name="directpath">17. Direct Path Loads</a></h2>

<p>Oracle Database Direct Path Loads allow data being inserted to bypass code
layers such as the database buffer cache. Also there are no INSERT statements
used. This can be very efficient for ingestion of huge amounts of
data. <em>Documentation link for further reading: <a
href="https://python-oracledb.readthedocs.io/en/latest/user_guide/batch_statement.html#direct-path-loads">Direct
Path Loads</a></em> </p>

<ul>
  <li><h4> 17.1 Using Direct Path Loads</h4>

    <p>The python-oracledb method <code>connection.direct_path_load()</code>
    accepts the schema name, table name, column names, and the data to be
    loaded.</p>

    <p>Review the code contained in <code>direct_path.py</code>:</p>

    <pre>import oracledb
import db_config

con = oracledb.connect(user=db_config.user, password=db_config.pw, dsn=db_config.dsn)

cur = con.cursor()

# Create table
cur.execute(
    """
    begin
        execute immediate 'drop table testdpl';
    exception when others then
        if sqlcode &lt;&gt; -942 then
            raise;
        end if;
    end;
    """
)
cur.execute(
    """
    create table testdpl (
        id   number(9),
        name varchar2(100)
    )
    """
)

DATA = [
    (1, "Adelaide"),
    (2, "Brisbane"),
    (3, "Canberra"),
]

con.direct_path_load(
    schema_name=db_config.user,
    table_name="testdpl",
    column_names=["id", "name"],
    data=DATA,
)

# Check the data was inserted
sql = "select * from testdpl"
for r in cur.execute(sql):
    print(r)
</pre>

    <p>Run the script in a terminal window:</p>

    <pre><strong>python direct_path.py</strong></pre>

    <p>This inserts the data into the new table and queries it back to
    confirm.</p>

    <p>For small numbers of rows, the elapsed time to insert data may not be
    better than using <code>executemany()</code>. For larger numbers of rows
    there can be time benefits and also reduced load on the database.</p>

  </li>

  <li><h4>17.2 Inserting DataFrames with Direct Path Loads</h4>

    <p>DataFrames from third-party libraries that support the Apache Arrow
    PyCapsule Interface can be inserted into Oracle Database by passing them
    directly to <code>direct_path_load()</code>.</p>

    <p>For this example, the <em>pandas</em> package need to be installed:</p>

    <pre>python -m pip install pandas --upgrade</pre>

    <p>Edit <code>direct_path.py</code> and import the Pandas package at the top:</p>

    <pre>import pandas</pre>

    <p>Change the data creation to use a Pandas DataFrame instead of the list
    of tuples:</p>

    <pre>d = {"A": [202, 412, 487], "B": ["Anna", "Bidisha", "Charlie"]}
DATA = pandas.DataFrame(data=d)
</pre>

    <p>Run the script in a terminal window:</p>

    <pre><strong>python direct_path.py</strong></pre>

    <p>This displays the inserted values from the DataFrame.</p>

  </li>
</ul>

<h2><a name="asyncio">18. Concurrent Programming with asyncio</a></h2>

<p>The Asynchronous I/O (asyncio) Python library can be used in python-oracledb
Thin mode for concurrent programming. This library allows you to run operations
in parallel, for example to run a long-running operation in the background
without blocking the rest of the application.</p>

<p>All python-oracledb synchronous methods that require a round-trip to the
database have corresponding asynchronous counterparts.  The methods
<code>oracledb.connect_async()</code> and
<code>oracledb.create_pool_async()</code> are used to create connections and
pools, respectively. <em>Documentation link for further reading: <a
href="https://python-oracledb.readthedocs.io/en/latest/user_guide/asyncio.html#concurrent-programming-with-asyncio">Concurrent
Programming with asyncio</a></em>.</p>

<ul>
  <li><h4> 18.1 Using asyncio</h4>

    <p>Review the code contained in <code>async_gather.py</code>:</p>

    <pre>import asyncio
import oracledb
import db_config

CONCURRENCY = 5   # Number of coroutines to run
POOLSIZE = 5      # Maximum connection pool size

SQL = """select unique current_timestamp as ct, sid||'-'||serial# as sidser
         from v$session_connect_info
         where sid = sys_context('userenv', 'sid')"""

async def init_session(connection, requested_tag):
    res = await connection.fetchone(SQL)
    print(res[0].strftime("%H:%M:%S.%f"), "- init_session SID-SERIAL#", res[1])

async def query(pool):
    async with pool.acquire() as connection:
        await connection.callproc("dbms_session.sleep", [1])
        res = await connection.fetchone(SQL)
        print(res[0].strftime("%H:%M:%S.%f"), "- query SID-SERIAL#", res[1])

async def main():

    pool = oracledb.create_pool_async(
        user=db_config.user,
        password=db_config.pw,
        dsn=db_config.dsn,
        min=1,
        max=POOLSIZE,
        session_callback=init_session,
    )

    coroutines = [query(pool) for i in range(CONCURRENCY)]

    await asyncio.gather(*coroutines)

    await pool.close()

asyncio.run(main())
</pre>

    <p>The application creates a connection pool using
    <code>create_pool_async()</code> and starts multiple coroutines that get a
    pooled connection and execute a query. Note that
    <code>create_pool_async()</code> is a synchronous call and is not
    awaited.</p>

    <p>The pool's <code>init_session()</code> method is invoked each time a
    connection is created by the pool. It displays the unique session
    identifier/serial number combination of the created connection.  Depending
    on the values of CONNECTION and POOLSIZE, and how fast your machine is, you
    may not see the specified maximum number of connections created.</p>

    <p>The main coroutine <code>query()</code> sleeps for a short time and then
    displays the unique session identifier/serial number of the connection used
    by that coroutine.</p>

    <p>When all the awaitables executed by <code>gather()</code> have
    completed, the pool is closed.</p>

    <p>Run the script in a terminal window:</p>

    <pre><strong>python async_gather.py</strong></pre>

    <p>The session identifier/serial numbers of connections created and used
    are displayed, along with timestamps.</p>

    <p>Experiment with different values of CONCURRENCY and POOLSIZE to see how
    the maximum poolsize affects the actual number of coroutines that are able
    to be doing work concurrently.</p>

</li>
</ul>

<h2> <a name="pipelining">19. Pipelining multiple operations</a></h2>

<p>Pipelining allows python-oracledb Thin mode applications to send multiple,
independent statements to Oracle Database with one call. The database is kept
busy processing them without waiting for the application to receive a result
set and send the next statement. While the database processes the pipeline of
statements, the application can continue with non-database work. When the
database has executed all the pipelined operations, their results are returned
to the application.  Effective use of Oracle Database Pipelining can increase
the responsiveness of an application and improve overall system throughput.
</p>

<p>Pipelining requires the use of asyncio.  True pipelining only occurs when
you are connected to Oracle Database version 23.  <em>Documentation link for
further reading: <a
href="https://python-oracledb.readthedocs.io/en/latest/user_guide/asyncio.html#pipelining-database-operations">Pipelining
Database Operations</a></em>.</p>

<ul>
  <li><h4> 19.1 Using Pipelining</h4>

    <p>Review the code contained in <code>pipelining.py</code>:</p>

    <pre>import asyncio
import oracledb
import db_config

async def get_weather():
    return "Hot and sunny"

async def get_location():
    return "Melbourne"

async def main():
    con = await oracledb.connect_async(
        user=db_config.user, password=db_config.pw, dsn=db_config.dsn
    )

    pipeline = oracledb.create_pipeline()
    pipeline.add_fetchone(
        "select ename, job from emp where empno = :en", [7839]
    )
    pipeline.add_fetchall("select dname from dept order by deptno")

    return_values = await asyncio.gather(
        get_weather(), get_location(), con.run_pipeline(pipeline)
    )

    for r in return_values:
        if isinstance(r, list):  # the pipeline return list
            for result in r:
                if result.rows:
                    for row in result.rows:
                        print(*row, sep="\t")
        else:
            print(r)  # a local operation result

    await con.close()

asyncio.run(main())
</pre>

    <p>Connection is established using <code>oracledb.connect_async()</code>.
    Asynchronous methods are awaited.</p>

    <p>The script creates a pipeline with <code>create_pipeline()</code> and
    adds two database operations.  The use of <code>asyncio.gather()</code>
    initiates parallel calls to the two local methods
    <code>get_weather()</code> and <code>get_location()</code>, and it also
    executes <code>run_pipeline()</code> which send the two database queries to
    Oracle Database.  Note although the database receives all the operations at
    the same time, it will execute each operation sequentially. The local
    Python work executes during the time the database is processing the
    queries.</p>

    <p>When all the awaitables executed by <code>gather()</code> have
    completed, the results are displayed.</p>

    <p>Run the script in a terminal window:</p>

    <pre><strong>python pipeline.py</strong></pre>

    <p>The weather, location, employee and department information is
    displayed.</p>

</li>
</ul>

<h2> <a name="thick">20. Python-oracledb Thick mode</a></h2>

<p>All the above examples were run in python-oracledb's default Thin mode that
connects directly to Oracle Database. Most could also have been run in Thick
mode too, by changing each <code>import db_config</code> line to <code>import
db_config_thick as db_config</code>.  Python-oracledb Thick mode uses Oracle
Client libraries (such as from Oracle Instant Client) to handle network
connectivity to Oracle Database.  There are some additional features these
libraries provide which are therefore only available in python-oracledb Thick
mode. The next example shows one of these. Other Oracle Database features that
require python-oracledb Thick mode include Application Continuity, and
Continuous Query Notification. <em>Documentation link for further reading: <a
href="https://python-oracledb.readthedocs.io/en/latest/user_guide/initialization.html#enabling-python-oracledb-thick-mode"
>Enabling python-oracledb Thick mode</a></em>.</p>

<p>The following sections assume you have installed the tutorial schema as
shown at the <a href="#setup" >tutorial start</a>.</p>

<ul>
<li>
<h4> 20.1 Review the Oracle Client library path</h4>

<p>You additionally need to make Oracle Client libraries available.  Follow the
documentation on <a
href="https://python-oracledb.readthedocs.io/en/latest/user_guide/installation.html"
>Installing python-oracledb</a>.</p>

<p>When you have installed Oracle Client libraries, review the library path
settings in <code>db_config_thick.py</code> file. If python-oracledb cannot
locate Oracle Client libraries, then your applications will fail with an error
like "<em>DPI-1047: Cannot locate a 64-bit Oracle Client library</em>". For our
examples, we are using Oracle Instant Client libraries.</p>

<pre>
# On Linux, this must be None.
# Instead, the Oracle environment must be set before Python starts.
instant_client_dir = None

# On Windows, if your database is on the same machine, comment these lines out
# and let instant_client_dir be None.  Otherwise, set this to your Instant
# Client directory.  Note the use of the raw string r"...", which allows backslashes to
# be used as directory separators.
if platform.system() == &quot;Windows&quot;:
    instant_client_dir = r"C:\Oracle\instantclient_23_7"

# On macOS set the directory to your Instant Client directory
if platform.system() == &quot;Darwin&quot;:
    instant_client_dir = os.environ.get("HOME")+"/Downloads/instantclient_23_3"

# You must always call init_oracle_client() to use thick mode
oracledb.init_oracle_client(lib_dir=instant_client_dir)</pre>

 <p> <strong>Important! </strong>Calling the <code>init_oracle_client()</code>
 function enables the Thick mode of python-oracledb. Once python-oracledb is in
 Thick mode, you cannot return to Thin mode without removing calls to
 <code>init_oracle_client()</code> and restarting the application.</p>

 <p>Edit <code>db_config_thick.py</code> and set
 <code>instant_client_dir</code> to <code>None</code> or to a valid path
 according to the following notes:</p>

  <ul>
    <li>

      <p>If you are on macOS or Windows, and you have installed Oracle Instant
      Client libraries because your database is on a remote machine, then set
      <code>instant_client_dir</code> to the path of the Instant Client
      libraries.</p>

    </li>

    <li>

      <p>If you are on Windows and have a local database installed, then
      comment out the two Windows lines, so that
      <code>instant_client_dir</code> remains <code>None</code>.</p>

    </li>

    <li>

      <p>In all other cases (including Linux with Oracle Instant Client), make
      sure that <code>instant_client_dir</code> is set to <code>None</code>.
      In these cases you must make sure that the Oracle libraries from Instant
      Client or your ORACLE_HOME are in your system library search path before
      you start Python. On Linux, the path can be configured with
      <em>ldconfig</em> or with the <em>LD_LIBRARY_PATH</em> environment
      variable.</p>

    </li>
  </ul>
</li>

<li>
  <h4 id="thickconfig">20.2 Review the configuration files for thick mode</h4>

  <p>Review <code>db_config_thick.py</code> (thick mode), and
  <code>sql/db_config.sql</code> files in the <code>tutorial</code> directory.
  These are included in other Python and SQL files for setting up the database
  connection.</p>

  <p>Edit <code>db_config_thick.py</code> and change the default values to
  match the connection information for your environment.  Alternatively, you
  can set the given environment variables in your terminal window. For example,
  the default username is "<em>pythondemo</em>" unless the environment variable
  "<em>PYTHON_USER</em>" contains a different username. The default connection
  string is for the '<em>freepdb1</em>' database service on the same machine as
  Python. In Python Database API terminology, the connection string parameter
  is called the "data source name" or "dsn".  Using environment variables is
  convenient because you will not be asked to re-enter the password when you
  run scripts:</p>

<pre>
user = os.environ.get("PYTHON_USER", "pythondemo")

dsn = os.environ.get("PYTHON_CONNECT_STRING", "localhost/freepdb1")

pw = os.environ.get("PYTHON_PASSWORD")
if pw is None:
    pw = getpass.getpass("Enter password for %s: " % user)
</pre>

<p>Also, change the default username and connection string in the SQL
configuration file <code>sql/db_config.sql</code>:</p>

<pre>
-- Default database username
def user = "pythondemo"

-- Default database connection string
def connect_string = "localhost/freepdb1"

-- Prompt for the password
accept pw char prompt 'Enter database password for &amp;user: ' hide
</pre>


  <p>The tutorial instructions may need adjusting, depending on how you have
  set up your environment.</p>

  </li>
</ul>

<p>The following section is specific to the python-oracledb Thick mode in this
release of python-oracledb.</p>

<h2><a name="soda">21. Simple Oracle Document Access (SODA)</a></h2>

<p>Simple Oracle Document Access (SODA) is a set of NoSQL-style APIs.
  Documents can be inserted, queried, and retrieved from Oracle Database.  By
  default, documents are JSON strings.  SODA APIs exist in many languages. It
  is usable in python-oracledb's thick mode. <i>Documentation link for further
  reading: <a
  href="https://python-oracledb.readthedocs.io/en/latest/user_guide/soda.html"
  >Simple Oracle Document Access (SODA)</a></i>.</p>

<ul>

  <li>
    <h4>21.1 Inserting JSON Documents</h4>

    <p>Review <code>soda.py</code> :</p>

<pre>
import oracledb
import db_config_thick as db_config

con = oracledb.connect(user=db_config.user, password=db_config.pw, dsn=db_config.dsn)

soda = con.getSodaDatabase()

# Explicit metadata is used for maximum version portability
metadata = {
               "keyColumn": {
                   "name":"ID"
               },
               "contentColumn": {
                   "name": "JSON_DOCUMENT",
                   "sqlType": "BLOB"
               },
               "versionColumn": {
                   "name": "VERSION",
                   "method": "UUID"
               },
               "lastModifiedColumn": {
                   "name": "LAST_MODIFIED"
               },
               "creationTimeColumn": {
                   "name": "CREATED_ON"
               }
           }

collection = soda.createCollection("friends", metadata)

content = {'name': 'Jared', 'age': 35, 'address': {'city': 'Melbourne'}}

doc = collection.insertOneAndGet(content)
key = doc.key

doc = collection.find().key(key).getOne()
content = doc.getContent()
print('Retrieved SODA document dictionary is:')
print(content)</pre>

    <p><code>soda.createCollection()</code> will create a new collection, or
    open an existing collection, if the name is already in use. (Due to a
    change in the default "<em>sqlType</em>" storage for Oracle Database 21c,
    the metadata is explicitly stated to use a BLOB column. This lets the
    example run with different client and database versions).</p>

    <p><code>insertOneAndGet()</code> inserts the content of a document into
    the database and returns a SODA Document Object.  This allows access to
    metadata such as the document key. By default, document keys are
    automatically generated.</p>

    <p>The <code>find()</code> method is used to begin an operation that will
    act upon documents in the collection.</p>

    <p><code>content</code> is a dictionary.  You can also get a JSON string by
    calling <code>doc.getContentAsString()</code>.</p>

    <p>Run the script in a terminal window:</p>

<pre><strong>python soda.py</strong></pre>

   <p>The output shows the content of the new document.</p>

    </li>

    <li>
      <h4>21.2 Searching SODA Documents</h4>

      <p>Extend <code>soda.py</code> to insert some more documents and perform
      a find filter operation:</p>

<pre>
my_docs = [
    {'name': 'Gerald', 'age': 21, 'address': {'city': 'London'}},
    {'name': 'David', 'age': 28, 'address': {'city': 'Melbourne'}},
    {'name': 'Shawn', 'age': 20, 'address': {'city': 'San Francisco'}}
]
collection.insertMany(my_docs)

filter_spec = { "address.city": "Melbourne" }
my_documents = collection.find().filter(filter_spec).getDocuments()

print('Melbourne people:')
for doc in my_documents:
    print(doc.getContent()["name"])
</pre>

      <p>Run the script again:</p>

      <pre><strong>python soda.py</strong></pre>

      <p>The find operation filters the collection and returns documents where
      the city is Melbourne.  Note the <code>insertMany()</code> method is
      currently in preview.</p>

      <p>SODA supports query by example (QBE) with an extensive set of
      operators.  Extend <code>soda.py</code> with a QBE to find documents
      where the age is less than 25:</p>

<pre>
filter_spec = {'age': {'$lt': 25}}
my_documents = collection.find().filter(filter_spec).getDocuments()

print('Young people:')
for doc in my_documents:
    print(doc.getContent()["name"])
</pre>

      <p>Running the script displays the names.</p>

    </li>
  </ul>

  <h2><a name="summary">Summary</a></h2>

  <p>In this tutorial, you have learned how to: </p>

  <ul>
    <li>Install the python-oracledb driver and use Thin and Thick modes</li>
    <li>Create and work with connections, connection pooling and Database Resident Connection Pooling</li>
    <li>Execute queries and fetch data</li>
    <li>Use bind variables</li>
    <li>Use PL/SQL stored functions and procedures</li>
    <li>Extend python-oracledb classes</li>
    <li>Work with LOB, JSON, and VECTOR data types</li>
    <li>Work with named objects</li>
    <li>Use Oracle Advanced Queuing</li>
    <li>Work with dataframes</li>
    <li>Use asynchronous programming and Pipelining</li>
    <li>Use scrollable cursors</li>
    <li>Use the SODA document store API</li>
  </ul>

  <p>For further reading, see the <a
  href="https://python-oracledb.readthedocs.io/en/latest/index.html" >python-oracledb documentation</a>.</p>

  <h2><a name="primer">Appendix: Python Primer</a></h2>

  <p>Python is a dynamically typed scripting language. It is most often used to
  run command-line scripts but is also used for web applications and web
  services.</p>

  <h4>Running Python</h4>

<p> You can either:</p>

  <ul>

    <li>

      <p>Create a file of Python commands, such as <code>myfile.py</code>.
      This can be run with:</p>

      <pre><strong>python myfile.py</strong></pre>

    </li>

    <li>

      <p>Alternatively run the Python interpreter by executing the
      <code>python</code> command in a terminal, and then interactively enter
      commands. Use <strong>Ctrl-D</strong> to exit back to the operating
      system prompt.</p>

    </li>

  </ul>

  <p>On some machines, you may need to run the <code>python3</code> command
  instead of <code>python</code>. </p>

  <p>When you run scripts, Python automatically creates bytecode versions of
  them in a folder called <code>__pycache__</code>.  These improve the
  performance of scripts that are run multiple times. They are automatically
  recreated if the source file changes.</p>

  <h4>Indentation</h4>

  <p>Whitespace indentation is significant in Python.  When copying examples,
  use the same column alignment as shown. The samples in this tutorial use
  spaces, not tabs. </p>

  <p>The following indentation prints 'done' once after the loop has
  completed:</p>

<pre>
for i in range(5):
    print(i)
print('done')
</pre>

  <p>But this indentation prints 'done' in each iteration:</p>

<pre>
for i in range(5):
    print(i)
    print('done')
</pre>

<h4>Strings</h4>

  <p>Python strings can be enclosed in single or double quotes:</p>

  <pre>'A string constant'
&quot;another constant&quot;</pre>
  <p>Multi line strings use a triple-quote syntax:</p>
  <pre>&quot;&quot;&quot;
SELECT *
FROM EMP
&quot;&quot;&quot;</pre>

  <h4>Variables</h4>

  <p>Variables do not need types declared:</p>

  <pre>count = 1
ename = 'Arnie'</pre>

  <h4>Comments</h4>

  <p>Comments can be single line:</p>

  <pre># a short comment</pre>

  <p>Or they can be multi-line using the triple-quote token to create a string
  that does nothing:</p>

  <pre>&quot;&quot;&quot;
a longer
comment
&quot;&quot;&quot;
</pre>

  <h4>Printing</h4>

  <p>Strings and variables can be displayed with a <code>print()</code>
  function:</p>

  <pre>print('Hello, World!')
print('Value:', count)</pre>

  <h4>Data Structures</h4>

  <p>Associative arrays are called 'dictionaries':</p>

  <pre>a2 = {'PI':3.1415, 'E':2.7182}</pre>

  <p>Ordered arrays are called 'lists':</p>

  <pre>a3 = [101, 4, 67]</pre>

  <p>Lists can be accessed via indexes.</p>

  <pre>
print(a3[0])
print(a3[-1])
print(a3[1:3])
</pre>

  <p>Tuples are like lists but cannot be changed once they are created. They
  are created with parentheses:</p>

  <pre>a4 = (3, 7, 10)</pre>

  <p>Individual values in a tuple can be assigned to variables like:</p>

  <pre>v1, v2, v3 = a4</pre>

  <p>Now the variable v1 contains 3, the variable v2 contains 7 and the
  variable v3 contains 10.</p>

  <p>The value in a single entry tuple like "<code>(13,)</code>"can be assigned
  to a variable by putting a comma after the variable name like:</p>

  <pre>v1, = (13,)</pre>

  <p>If the assignment is:</p>

  <pre>v1 = (13,)</pre>

  <p>then <code>v1</code> will contain the whole tuple "<code>(13,)</code>"</p>

<h4>Objects</h4>

<p>Everything in Python is an object. As an example, given the of the list
<code>a3</code> above, the <code>append()</code> method can be used to add a
value to the list.</p>

<pre>a3.append(23)</pre>

<p>Now <code>a3</code> contains <code>[101, 4, 67, 23]</code></p>

<h4>Flow Control</h4>

<p>Code flow can be controlled with tests and loops. The
<code>if</code>/<code>elif</code>/<code>else</code> statements look like:</p>

<pre>
if v == 2 or v == 4:
    print('Even')
elif v == 1 or v == 3:
    print('Odd')
else:
    print('Unknown number')
</pre>

<p>This also shows how the clauses are delimited with colons, and each
sub-block of code is indented.</p>

<h4>Loops</h4>

<p>A traditional loop is:</p>

<pre>for i in range(10):
    print(i)</pre>

<p>This prints the numbers from 0 to 9. The value of <code>i</code> is
incremented in each iteration. </p>

<p>The '<code>for</code>' command can also be used to iterate over lists and
tuples:</p>

<pre>
a5 = ['Aa', 'Bb', 'Cc']
for v in a5:
    print(v)
</pre>

<p>This sets <code>v</code> to each element of the list <code>a5</code> in turn.</p>

<h4>Functions</h4>

<p>A function may be defined as:</p>

<pre>
def myfunc(p1, p2):
    &quot;Function documentation: add two numbers&quot;
    print(p1, p2)
    return p1 + p2</pre>

<p>Functions may or may not return values. This function could be called
using:</p>

<pre>v3 = myfunc(1, 3)</pre>

<p>Function calls must appear after their function definition.</p>

<p>Functions are also objects and have attributes. The inbuilt
<code>__doc__</code> attribute can be used to find the function
description:</p>

  <pre>print(myfunc.__doc__)</pre>

<h4>Modules</h4>

<p>Sub-files can be included in Python scripts with an import statement.</p>

<pre>import os
import sys</pre>

<p>Many predefined modules exist, such as the os and the sys modules.</p>

<p>Other modules, such as python-oracledb, need to be installed separately.
These can be installed from PyPI, for example from <a
href="https://pypi.org/project/oracledb/" >pypi.org/project/oracledb/</a> (as
shown in the tutorial <a href="#setup" >setup</a> instructions). In some
environments you may prefer to install using a package repository such as <a
href="https://yum.oracle.com/oracle-linux-python.html" >yum.oracle.com</a> for
Oracle Linux.</p>

<p>After installing python-oracledb, your Python scripts can import the driver
using:</p>

<pre>import oracledb</pre>

<h2><a name="resources">Resources</a></h2>

<ul>
  <li><a href="https://docs.python.org/3/" >Python Documentation</a></li>

  <li><a href="http://python-oracledb.readthedocs.io/en/latest/index.html"
  >Python python-oracledb Documentation</a></li>

  <li><a href="https://github.com/oracle/python-oracledb/tree/main/samples"
  >Python-oracledb Source Code Repository Samples</a></li>

  <li><a
  href="https://www.oracle.com/database/technologies/appdev/python/quickstartpythononprem.html"
  >Quick Start: Developing Python Applications for Oracle Database
  (On-Premises)</a></li>

  <li><a
  href="https://www.oracle.com/database/technologies/appdev/python/quickstartpython.html">Quick
  Start: Developing Python Applications for Oracle Autonomous Database</a></li>

</ul>

<div class="footer"></div>

<hr/>
<h2>License</h2>

<p>Copyright &copy; 2017, 2025, Oracle and/or its affiliates.</p>

<p>This software is dual-licensed to you under the Universal Permissive License
(UPL) 1.0 as shown at https://oss.oracle.com/licenses/upl and Apache License
2.0 as shown at http://www.apache.org/licenses/LICENSE-2.0. You may choose
either license. </p>

<p>If you elect to accept the software under the Apache License, Version 2.0,
the following applies:</p>

<p>Licensed under the Apache License, Version 2.0 (the "License"); you may not
use this file except in compliance with the License.  You may obtain a copy of
the License at https://www.apache.org/licenses/LICENSE-2.0</p>

<p>Unless required by applicable law or agreed to in writing, software
distributed under the License is distributed on an "AS IS" BASIS, WITHOUT
WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.  See the
License for the specific language governing permissions and limitations under
the License. </p>

</body>
</html>
